SQL Server database commonly used T

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 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 allocation errors, structure rows, or pages.

and deleting corrupted text objects, these fixes can 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.