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.

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 ORDER BY 2 DESC

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily