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

server| Data | database | Statement SQL SERVER Database Management Common 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

10. View information about users and processes in the database
sp_who
View information about active users and processes in the SQL Server database
sp_who ' Active '
To view the locks in the SQL Server database
Sp_lock

The process number 1--50 is used internally within the SQL Server system, and the process number greater than 50 is the user's connection process.
The SPID is the process number, dbid is the database number, ObjID is the data object number
To view the SQL statement that the process is executing
DBCC INPUTBUFFER ()

We recommend that you use the improved SP_WHO3 process to see directly the SQL statements that the process runs
Sp_who3

Check for deadlocks with the Sp_who_lock process
Sp_who_lock

11. How to shrink a database log file
Shrink the simple recovery mode database log, after shrinking the @database_name_log size unit m
Backup LOG @database_name with NO_LOG
DBCC SHRINKFILE (@database_name_log, 5)
12. Methods for analyzing SQL Server SQL statements:

Set STATISTICS Time {on | off}
Set STATISTICS IO {on | off}
Graphical display of query execution plans

In Query Analyzer-> Query-> show estimated evaluation plan (D)-ctrl-l or click on the graphics in the toolbar

Text mode display Query execution plan
Set SHOWPLAN_ALL {on | off}

SET SHOWPLAN_TEXT {on | off}
Set STATISTICS Profile {on | off}

13. When inconsistent error, NT Event Viewer out of error No. 3624, repair the database method

Comment out the tables referenced in the application for inconsistencies and then restore and repair them on the backup or other machines first

ALTER DATABASE [@error_database_name] set Single_user

To fix a table that appears inconsistent errors

DBCC CHECKTABLE (' @error_table_name ', Repair_allow_data_loss)

Or, unfortunately, choose to fix a small database name with inconsistent errors

DBCC CHECKDB (' @error_database_name ', Repair_allow_data_loss)
ALTER DATABASE [@error_database_name] set Multi_user
CHECKDB has 3 parameters:
Repair_allow_data_loss includes assigning and unassign rows and pages to correct assignment errors, structural rows or pages, and deleting corrupted text objects that may result in some loss of data.
A repair operation can be completed under a user transaction to allow the user to roll back the changes.
If you roll back the repair, the database will still contain errors and should be recovered from the backup.
If an incorrect fix is omitted due to the level of repair provided, any fixes that depend on the repair will be omitted.
When the repair is complete, back up your database.
Repair_fast for small, time-consuming fixes, such as fixing additional keys in a nonclustered index.
These fixes can be done quickly, and there is no risk of losing data.
Repair_rebuild performs all the repairs performed by Repair_fast, including the need for a longer period of repair, such as rebuilding an index.
There is no risk of losing data when performing these repairs.