SQL Queries For DBA

Getting Info On SQL server Database.

select * from master..sysdatabases


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 (FirstNameLastName
SELECT FirstNameLastName
FROM Employee


Getting Information About server.

select @@version
select @@servername
select serverproperty('servername')

select @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

select system_user

select user

select @@language as 'language Name'
select @@servicename as 'Service Name'

To get list of the users having sysadmin privilege
SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser
  FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1

Backup Related Information.

SELECT db.name, 
case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100), 
	MAX(b.backup_finish_date)) end AS last_backup_finish_date
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
	WHERE db.database_id NOT IN (2) 
GROUP BY db.name

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily