SQL Server Common Administration commands summary _mssql

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 [url=mailto: ' @procedure_name '] ' @procedure_name ' [/url]
View the name of a data object that contains a string @str
SELECT DISTINCT object_name (ID) from syscomments where text like [Url=mailto: '% @str% '] '% @str% ' [/url]
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 a database article file
View all database article file sizes
DBCC SQLPERF (LOGSPACE)
If some of the article files are large, shrink the simple recovery mode database article, after shrinking the @database_name_log size unit is M
Backup LOG @database_name with NO_LOG
DBCC SHRINKFILE (@database_name_log, 5)
13. See where the database is
SELECT * FROM Sysfiles