SQL Server manages common SQL and T-SQL

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

Several common types of SQL Server patched version numbers:

8.00.194 Microsoft SQL Server 2000
8.00.384 Microsoft SQL Server SP1
8.00.532 Microsoft SQL Server SP2
8.00.760 Microsoft SQL Server SP3
8.00.818 Microsoft SQL Server SP3 w/cumulative Patch ms03-031
8.00.2039 Microsoft SQL Server SP4

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

3. View database-initiated parameters
sp_configure

4. Check the 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

Fix_orphan_user scripts or Loneuser procedures that can be used when repairing orphaned users 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

To 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 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 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

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

10. 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

11. Ways 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 model database log, and the size of the @database_name_log is in M
Backup LOG @database_name with NO_LOG
DBCC SHRINKFILE (@database_name_log, 5)

12. 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}


13. When there is an inconsistency error, error No. 3624 in NT Event Viewer, 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 errors in allocation errors, structure lines, or pages.
and remove corrupted text objects, these fixes 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.

SQL Server manages common SQL and T-SQL