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 @@versionselect @@servernameselect serverproperty('servername')select @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'select system_userselect userselect @@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 DESCSELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

To get the current privileges allocated to users.

select prin.name,perm.*from sys.server_permissions as permjoin sys.server_principals as prinon perm.grantee_principal_id=prin.principal_id
exec xp_readerrorlogexec xp_readerrorlog 1 // error log 1