Store Procedure Utility

To view the definition of a stored procedure: 

sp_helptext procedure_name 

To view the information about a stored procedure: 

sp_help procedure_name 

To view the dependencies of the stored procedure: 

sp_depends procedure_name

To view list of all Procedures and Function

SP_STORED_PROCEDURES

Usefull link

http://www.quest-pipelines.com/newsletter-v3/0802_A.htm

Playing with tables in BULK

-- Generates Drop table Commands for All Tables

SELECT 'DROP TABLE ' + table_name

FROM information_schema.TABLES

WHERE table_type = 'BASE TABLE'

-- Loops for each table in the database

exec sp_msforeachtable 'print ''?'''

exec sp_msforeachtable 'truncate table ?'

-- Delete All Constrainsts of Table

DECLARE @database nvarchar(50)

DECLARE @table nvarchar(50)

set @database = 'DNNDB'

set @table = 'DNN_Forum_Threads'

DECLARE @sql nvarchar(255)

WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)

BEGIN

    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 

    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

    where    constraint_catalog = @database and 

            table_name = @table

    exec    sp_executesql @sql

END

-- Delete all tables using CURSOR

DECLARE tables_cursor CURSOR

FOR SELECT name FROM sysobjects WHERE type = ‘U’

OPEN tables_cursor

DECLARE @tablename sysname

FETCH NEXT FROM tables_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXEC (’DROP TABLE ‘ + @tablename)

FETCH NEXT FROM tables_cursor INTO @tablename

END

DEALLOCATE tables_cursor

Generating dates Iteratively

First of all we declare 2 variables defining the start date where to start from second is numbe of months for which we want to loop.

We use ;with iteratation to acheive this tasks that selects each date and than add a day into it till its condition is true.

Folowing is the code snippet that generates all days in a month month.


DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '20080101', @NumberOfMonths = 1
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT * FROM Days

Folowing is the code snippet that generates next 12 months from the current date.


DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '20080101', @NumberOfMonths = 12
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(m, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(m, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT CONVERT(char(3), CalendarDay, 100) AS monthName,DATEPART(mm,CalendarDay) AS MonthNo FROM Days

Further, user can play with it with combination with other table to acheive desired results.

Listing database constarints

List all table of database with there constraints. Its a useful query to list all database constraints. Yet a simple query but useful You can further filter them by table name , constraint type etc.

The query simply query sys.objects and fetches tables and there constraints. SCHEMA_NAME a scaler function returns schema's name by id. and OBJECT_NAME returns Object(table, view etc) name by its ID. type_desc defines the scripts constraint type (Foreign key, Primary key, default etc).

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects

Using some filters (lists all foreign key contraints in DB)

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE 'FOREIGN_KEY_CONSTRAINT'

Following query list down all FK contstraints of tabel specified

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE 'FOREIGN_KEY_CONSTRAINT' AND OBJECT_NAME(parent_object_id)=''

Further Improved version

select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
and o2.name='

Happy Coding :)