Database-related Common query 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-initiated parameters

sp_configure

4 , view database startup time

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

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 a database with sql:

Sp_renamedb ' Old_dbname ', ' new_dbname '

6 , view all database user login information

Sp_helplogins

To view the role information that all database users belong to

Sp_helpsrvrolemember

Fixes a fix_orphan_user script or loneuser process that can be used when you isolate a user when migrating a server.

Change the user owner of a data object

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

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

Back Up Database user login information on one server 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 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

The clustered index is the physical order in which the records are arranged, and the index occupies less space.

Tables with very frequent key-value DML operations I recommend using non-clustered indexes and constraints, with default values for the FILLFACTOR parameter.

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 data object names that contain a string @str

SELECT DISTINCT object_name (ID) from syscomments where 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.

Ten , 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

One , how to shrink the database log file

Shrink the simple recovery mode database log, and the size of the @database_name_log after shrinking is M.

Backup LOG @database_name with NO_LOG DBCC SHRINKFILE (@database_name_log, 5)

A , methods for parsing SQL Server SQL statements

Set STATISTICS Time {on | off} Set STATISTICS IO {on | off}

Graphical display of query execution plans

Show 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}

- , there is an inconsistency error, the NT Event Viewer out of error No. 3624, the method of repairing the database.

First comment out the list of inconsistencies that are referenced in the application, and then restore and repair the backup or other machine first.

ALTER DATABASE [@error_database_name] set Single_user

Fix tables with inconsistent errors

DBCC CHECKTABLE (' @error_table_name ', Repair_allow_data_loss)

Or, unfortunately, choose to fix small database names with inconsistent errors.

DBCC CHECKDB (' @error_database_name ', Repair_allow_data_loss) ALTER DATABASE [@error_database_name] set Multi_user

The CHECKDB has 3 parameters:

Repair_allow_data_loss includes assigning and reassigning rows and pages to correct allocation errors, errors in structure rows or pages, and deleting corrupted text objects, which can cause some data loss.

The repair operation can be completed under a user transaction to allow the user to roll back the changes.

If the repair is rolled back, the database will still contain errors and should be recovered from the backup.

If a bug's fix is omitted due to the level of repair provided, any fix that depends on the fix will be omitted.

After the repair is complete, back up the database.

Repair_fast perform small, time-consuming repair operations, such as repairing 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 done by repair_fast, including repairs that take a long time (such as rebuilding the index).

There is no risk of data loss when performing these repairs.

Database-related Common query statements