SQL Server database manages commonly used SQL and T-SQL statements

1. View the version of the database

SELECT @ @version

2. View the machine operating system parameters of the database

EXEC master.. xp_msver

3. View Database Startup Parameters

sp_configure

4. View Database Startup time

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

To view the database server name and instance name

print ' Server Name ... ...: ' + CONVERT (varchar (), @ @SERVERNAME), ".

print ' Instance ....: ' + CONVERT (varchar), @ @SERVICENAME. @)., d..

5. View all database names and sizes

sp_helpdb

To rename SQL for a database

Sp_renamedb ' Old_dbname ', ' new_dbname '

6. View all database user login information

Sp_helplogins

View the role information that all database users belong to

Sp_helpsrvrolemember

Fix_orphan_user scripts or Loneuser procedures that can be used when repairing orphaned users when migrating servers

Change the user owner of a data object

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

Note: Changing any part of an object name can corrupt scripts and stored procedures.

To back up the database user login information on a single server, you can use Add_login_to_aserver script

7. View Linked Servers

Sp_helplinkedsrvlogin

View Remote database user logon information

Sp_helpremotelogin

8. View the size of a data object under a database

sp_spaceused @objname

You can also see the largest n (default 50) table with the Sp_toptables procedure

View index information for a data object under a database

Sp_helpindex @objname

You can also use the Sp_nchelpindex procedure to view more detailed indexing

Sp_nchelpindex @objname

Clustered index is the physical order of records, the index occupies less space.

Tables with very frequent key-value DML operations I recommend that you use a clustered index and a constraint, and the FILLFACTOR parameter will have a default value.

To view the constraint information for a data object under a database

Sp_helpconstraint @objname

9. View all stored procedures and functions in the database

Use @database_name

Sp_stored_procedures

To view the source code for stored procedures and functions

Sp_helptext ' @procedure_name '

View the name of a data object that contains 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 in front of AS

Decryption of encrypted stored procedures and functions can be done using the Sp_decrypt procedure