SQL Stuff

[2010.05.21] Scripting all DB objects in SQL Server database
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

[2010.05.05] Ever want to generate a sequence of numbers in T-SQL? Here’s a cool way w/o using temp tables.
http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers.aspx

[2010.04.30] Cool sproc to delete all data from table in a database. Great for testing! Just be sure you *are* on your test database.
http://www.eggheadcafe.com/tutorials/aspnet/188b19de-6301-4116-ba10-143fbceffe97/sql-server–delete-recor.aspx

Create Procedure dbo.sp_EmptyAllTables (@ResetIdentity Bit)
As
Begin
    Declare @SQL VarChar(500)
    Declare @TableName VarChar(255)
    Declare @ConstraintName VarChar(500)
   
 Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type=’FOREIGN KEY’   
 Open curAllForeignKeys

    Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
    While @@FETCH_STATUS=0
    Begin
        Set @SQL = ‘ALTER TABLE ‘ + @TableName + ‘ NOCHECK CONSTRAINT ‘ + @ConstraintName
        Execute(@SQL)
        Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
    End

    Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE=’BASE TABLE’
    Open curAllTables

    Fetch Next From curAllTables INTO @TableName
    While @@FETCH_STATUS=0
    Begin
        Set @SQL = ‘DELETE FROM ‘ + @TableName
  If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),’TableHasIdentity’)=1
     Set @SQL = @SQL + ‘; DBCC CHECKIDENT(”’ + @TableName + ”’,RESEED,0)’             

        Execute(@SQL)
        Fetch Next From curAllTables INTO @TableName
    End
 
    Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName

    While @@FETCH_STATUS=0
    Begin
        Set @SQL = ‘ALTER TABLE ‘ + @TableName + ‘ CHECK CONSTRAINT ‘ + @ConstraintName
        Execute(@SQL)
        Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
    End

    Close curAllTables  
    Deallocate curAllTables

    Close curAllForeignKeys
    Deallocate curAllForeignKeys  
End