:::: MENU ::::

Beginner SQL Server Cheat Sheet for DB Administration

  • Nov 05 / 2008
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Beginner SQL Server Cheat Sheet for DB Administration

 -- Create PKey constrainton existing column  
 ALTER TABLE TableName  
 ADD CONSTRAINT PKeyName  
 PRIMARY KEY CLUSTERED (PKeyColumn)  
 GO  


 -- Create Fkey Constraint  
 ALTER TABLE FKeyTable ADD CONSTRAINT  
 FKeyName FOREIGN KEY (FKeyColumn)  
 REFERENCES PKeyTable (PKeyColumn)  
 ON UPDATE NO ACTION  
 ON DELETE NO ACTION  
 GO  


 -- Create check constraint  
 ALTER TABLE TableName WITH NOCHECK ADD CONSTRAINT  
 ConstraintName CHECK ( ConstraintExpression)  
 GO  


 -- Create Unique key constraint  
 ALTER TABLE TableName ADD CONSTRAINT  
 UKeyName UNIQUE NONCLUSTERED (col1,col2)  
 GO  


 -- Craete clustered/non clustered index  
 CREATE CLUSTERED INDEX IndexName ON TableName  
 (ColumnsHere) ON [PRIMARY]  
 GO  


 --Retreive data from excel file  
 select [Server], [Orphaned Logins]  
 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  
 'Data Source= 'Path here';Extended Properties=Excel 8.0')...[Sheet1$]  
 order by [Orphaned Logins] asc

  
 -- Info about locks  
 sp_lock  
 SELECT * FROM sys.dm_tran_locks  
 SELECT * FROM sys.dm_exec_requests  


 -- Track active trans on SQL Server 2005 or 2008  
 SELECT * FROM sys.dm_tran_session_transactions  


 ---------List columns in table  
 SELECT Column_Name, Data_Type  
 FROM information_schema.columns  
 WHERE table_name = 'authors'  


 ----Current Connections to a DB  
 SELECT hostname, program_name,loginame,  
 cpu, physical_io, memusage, login_time,  
 last_batch, [status]  
 FROM master.dbo.sysprocesses  
 WHERE db_name(dbid) = 'DBNameHere'  
 ORDER BY hostname  


 -- identify longest open transaction  
 DBCC OPENTRAN('DBNameHere')  
 GO  


 -- get info about object  
 sp_help ObjectNameHere  


 -- Get list of all table valued UDF in DB  
 SELECT name, crdate  
 FROM sys.sysobjects  
 WHERE XTYPE = 'U'  
 --AF = Aggregate function (CLR)  
 --C = CHECK constraint  
 --D = Default or DEFAULT constraint  
 --F = FOREIGN KEY constraint  
 --L = Log  
 --FN = Scalar function  
 --FS = Assembly (CLR) scalar-function  
 --FT = Assembly (CLR) table-valued function  
 --IF = In-lined table-function  
 --IT = Internal table  
 --P = Stored procedure  
 --PC = Assembly (CLR) stored-procedure  
 --PK = PRIMARY KEY constraint (type is K)  
 --RF = Replication filter stored procedure  
 --S = System table  
 --SN = Synonym  
 --SQ = Service queue  
 --TA = Assembly (CLR) DML trigger  
 --TF = Table function  
 --TR = SQL DML Trigger  
 --TT = Table type  
 --U = User table  
 --UQ = UNIQUE constraint (type is K)  
 --V = View  
 --X = Extended stored procedure 
Consult us to explore the Databases. Contact us