SQL Queries For DBA

Getting Info On SQL server Database.

select * from master..sysdatabases

sp_databases

To get list of table of particular database.

SELECT name FROM sysobjects WHERE xtype = 'U' -- Tables

SELECT name FROM sysobjects WHERE xtype = 'V' -- Views

SELECT name FROM sysobjects WHERE xtype = 'P' -- Stored Procedures

select TABLE_NAME,Table_Type from information_schema.tables

where Table_Type = 'BASE TABLE'

select * from sys.tables

sp_tables -- To get list of Tables and views

==

Various Other objects

SELECT sobjects.name FROM sysobjects sobjects

WHERE sobjects.xtype = '?'

List of object xtype below

C: Check constraint

D: Default constraint

F: Foreign Key constraint

L: Log

P: Stored procedure

PK: Primary Key constraint

RF: Replication Filter stored procedure

S: System table

TR: Trigger

U: User table

UQ: Unique constraint

V: View

X: Extended stored procedure

=========================================

Creating Backup of Tables inside the database.

SELECT * INTO Backup_Userinfo FROM Userinfo;

However it does not define primary key.

If table is already created then.

INSERT INTO TestTable (FirstName, LastName)

SELECT FirstName, LastName

FROM Employee

=========================================

Getting Information About server.

==========================================

To get list of the users having sysadmin privilege

==========================================

Backup Related Information.

To get the current privileges allocated to users.