SQL Server manages commonly used SQL and T-SQL

1. View the version of the database

SELECT @ @version

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

View Object-level user permissions under a database

Sp_helprotect