SQL SERVER Common Commands

Red for common

0, Row_number () over and data combination sale/cnt

Select *,row_number () over (order by ProductName) as RowNumber
From Products
Select Row_number () over (order by sale/cnt Desc) as sort, sale/cnt
From (
Select-60 as sale,3 as CNT from dual union
Select sale,6 as CNT from dual union
Select sale,5 as CNT from dual union
Select-20 as sale,2 as CNT from dual union
Select sale,8 as CNT from dual);

1. View the version of the database
SELECT @ @version

SELECT @ @connections//Returns the number of connections that SQL Server has attempted since it was last started, regardless of whether the connection succeeded or failed

SELECT @ @max_connections//Returns the maximum number of simultaneous user connections that the instance of SQL Server allows. The returned value is not necessarily the current configured value

SELECT @ @lock_timeout//Returns the current lock timeout setting (in milliseconds) for the current session.
2. View information about users and processes in the database
sp_who
View information about active users and processes in a SQL Server database
sp_who ' Active '
To view locks in a SQL Server database
Sp_lock

The process number 1--50 is internal to 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, and 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 directly see the SQL statements that the process is running
Sp_who3

Check for deadlocks with Sp_who_lock process
Sp_who_lock

3. View all database names and sizes
sp_helpdb

4. View database-initiated parameters
sp_configure

5. View all database user login information
Sp_helplogins

To view the role information that all database users belong to
Sp_helpsrvrolemember

6. View the size of a data object under a database
sp_spaceused @objname

7. 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 data object names that contain a string @str
SELECT DISTINCT object_name (ID) from syscomments where the text like '% @str% '

Create an encrypted stored procedure or function with the encryption parameter in front of AS

Decrypting encrypted stored procedures and functions can be done using the Sp_decrypt process

8. Methods for parsing SQL Server SQL statements:
Set STATISTICS Time {on | off}
Set STATISTICS IO {on | off}
Graphical display of query execution plans

Display estimated evaluation plan (D) in Query Analyzer-----ctrl-l or click on the graph in the toolbar

Display query execution plan in text mode
Set SHOWPLAN_ALL {on | off}

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

9. View the machine operating system parameters of the database
EXEC master. xp_msver

10. Check the database startup time
Select CONVERT (varchar (), login_time,120) from master: sysprocesses where spid=1

11. View Linked Servers
Sp_helplinkedsrvlogin

View Remote database user logon information
Sp_helpremotelogin

You can also use the sp_toptables process to see the maximum n (default 50) Table

View index information for a data object under a database
Sp_helpindex @objname

You can also use the Sp_nchelpindex process to see more detailed index conditions
Sp_nchelpindex @objname

View constraint information for a data object under a database
Sp_helpconstraint @objname

12. How to shrink a database log file
Shrink the simple Recovery model database log, @database_name_log size in units of M
Backup LOG @database_name with NO_LOG
DBCC SHRINKFILE (@database_name_log, 5)

13. Known column name, lookup table

Select Tab.name from syscolumns as col inner joins sysobjects as tab on Col.id=tab.id where col.name= ' Item_idx '

SQL SERVER Common Commands