SQL Server database management common SQL and T-SQL statements

SQL Server database management common SQL and T-SQL statements

1. view the database version

Select @ version

2. view the operating system parameters of the machine where the database is located

Exec master .. xp_msver

3. view database startup parameters

Sp_configure

4. view the database startup time

Select convert (varchar (30), login_time, 120) from master .. sysprocesses where spid = 1

View database server name and Instance name

Print 'server Name ...... + convert (varchar (30), @ SERVERNAME)

Print 'instance ......: '+ convert (varchar (30), @ SERVICENAME)

5. View All Database names and sizes

Sp_helpdb

SQL statement used to rename a database

Sp_renamedb 'old _ dbname', 'new _ dbname'

6. View logon information of all database users

Sp_helplogins

View the role information of all database users

Sp_helpsrvrolemember

Fixed the fix_orphan_user script or LoneUser process that can be used to isolate users during server migration.

Change the user owner of a Data Object

Sp_changeobjectowner [@ objectname =] 'object', [@ newowner =] 'owner'

Note: changing any part of the object name may corrupt the script and stored procedure.

You can use the add_login_to_aserver script to back up the database user logon information on a server.

7. view linked servers

Sp_helplinkedsrvlogin

View remote database user logon information

Sp_helpremotelogin

8. view the size of a data object in a database

Sp_spaceused @ objname

You can also use the sp_toptables process to view the maximum N tables (50 by default ).

View the index information of a data object in a database

Sp_helpindex @ objname

You can also use the SP_NChelpindex process to view more detailed indexes.

SP_NChelpindex @ objname

Clustered indexes sort records in physical order and occupy less space.

We recommend that you use non-clustered indexes and constraints for tables with frequent key value DML operations. The fillfactor parameter uses the default value.

View the constraints of a data object in a database

Sp_helpconstraint @ objname

9. view all stored procedures and functions in the database

Use @ database_name

Sp_stored_procedures

View the source code of stored procedures and functions

Sp_helptext '@ procedure_name'

View the Data Object Name containing a string @ str

Select distinct object_name (id) from syscomments where text like '% @ str %'

Create an encrypted stored procedure or function WITH the with encryption parameter before the

 

You can use sp_decrypt to decrypt encrypted stored procedures and functions.