Sql Server Script Generator

--Purpose: To Drop Trigger if Exists
--==========================

  


SELECT ' IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''' + name + ''')) ' 
     + ' DROP TRIGGER ' + name + ';' FROM dbo.sysobjects 
WHERE TYPE = 'TR'

--Purpose: To Drop Triggers name starts with  I_ or U_ or D_ if Exists
-----------------------------------------------------------------------------------

  


SELECT ' IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''' + name + ''')) ' 

     + ' DROP TRIGGER ' + name + ';' FROM dbo.sysobjects 

WHERE TYPE = 'TR' AND ( name LIKE 'I_%' OR name LIKE 'U_%' OR name LIKE 'D_%')

--To Reseed Table's Identity Column
--==========================

  


DBCC CHECKIDENT (PPP_tblHeadGroup, reseed, 1)    
DBCC CHECKIDENT (PPP_tblHeadGroup, reseed)

 


--Purpose: Data Cleansing Script Generator for Sql Server Database 
--======================================================

 


WITH Fkeys AS (
    SELECT DISTINCT OnTable.name AS OnTable,
            AgainstTable.name AS AgainstTable
    FROM   sysforeignkeys AS fk
    INNER JOIN sysobjects AS onTable ON fk.fkeyid = onTable.id
    INNER JOIN sysobjects AS againstTable ON fk.rkeyid = againstTable.id
    WHERE  1 = 1
    AND AgainstTable.TYPE = 'U'
    AND OnTable.TYPE = 'U'
    -- ignore self joins; they cause an infinite recursion
    AND OnTable.Name <> AgainstTable.Name),
MyData AS (
    SELECT o.name AS OnTable,
        FKeys.againstTable AS AgainstTable
    FROM   sys.objects AS o
    LEFT OUTER JOIN FKeys ON o.name = FKeys.onTable
    WHERE  1 = 1
    AND o.type = 'U'
    AND o.name NOT LIKE 'sys%'),
MyRecursion AS (-- base case
    SELECT OnTable AS TableName,
            1 AS Lvl
    FROM   MyData
    WHERE  1 = 1
    AND AgainstTable IS NULL
    -- recursive case
    UNION ALL
    SELECT OnTable AS TableName,
        r.Lvl + 1 AS Lvl
    FROM   MyData AS d
    INNER JOIN MyRecursion AS r ON d.AgainstTable = r.TableName)
SELECT MAX(Lvl) AS Lvl,
    TableName,
    'DELETE FROM ' + tablename + '; ' AS strSql,
    CASE WHEN (OBJECTPROPERTY(object_id(tablename), 'TableHasIdentity') = 1)
        THEN 'DBCC CHECKIDENT (' + tablename + ', RESEED, 1); DBCC CHECKIDENT (' + tablename + ', RESEED);' 
        ELSE '' END AS strReseed
FROM MyRecursion
GROUP BY TableName
ORDER BY 1 DESC, 2 DESC;

--Sources/Refs:
--http://www.jasinskionline.com/TechnicalWiki/List-Tables-in-Dependency-Order-SQL-Server.ashx?AspxAutoDetectCookieSupport=1
--http://www.devx.com/tips/Tip/32076