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