Useful SQL Commands in SQL SERVER

These are some very useful SQL Commands.

Truncate all tables:

EXEC sp_MSforeachtable 'TRUNCATE TABLE ?' 

Delete data from all tables:

EXEC sp_MSForEachTable "DELETE FROM ?" 

Disable all constraints from all tables:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Enable all constraints from all tables:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
 

Disable all triggers from all tables:

EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"

Enable all triggers from all tables:

EXEC sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER  all"  

Getting all column information of the table:

Select* fromINFORMATION_SCHEMA.COLUMNS

where table_name='Table Name'

Getting list of stored procedure modified in last N days:

SELECT name

FROMsys.objects

WHEREtype ='P'

ANDDATEDIFF(D,modify_date,GETDATE())< N

Note: Replace N with any number.
 

Getting a list of stored procedures created in the last N days:

SELECT name

FROMsys.objects

WHEREtype ='P'

ANDDATEDIFF(D,create_date,GETDATE())< N

Note: Replace N with any number.


Similar Articles