SQL Server Administration Common SQL statements

server|sqlserver| statement

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)

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

View Object-level user permissions under a database

Sp_helprotect

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 view and shrink database log files

View all database log file sizes

DBCC SQLPERF (LOGSPACE)

If some log files are large, shrink the simple recovery mode database log, and the @database_name_log size unit is 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.

Repai*_**st 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 repai*_**st, 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.