Useful SQL Commands in SQL SERVER

This article is about some very useful commands of 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.