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