Drop Constraint Key Script

Below Script will generate the Drop Script of all
the Constarints within a Database.
---Generates Script to Drop Constraints
(Primary Key or Unique Key)---
--USE (DatabaseName)
GO
SET NOCOUNT ON
--Declared Variables--
DECLARE @vNumDBs Int
DECLARE @vCount Int
DECLARE @SchemaName Varchar(MAX)
DECLARE @TableName Varchar(MAX)
DECLARE @ConstraintName Varchar(MAX)
----------------------
--Store Object and Schema Name--
DECLARE @Object_Holder TABLE (TabID int IDENTITY(1,1) ,
TableName varchar(max),Schemaname varchar(max))
INSERT INTO @Object_Holder(TableName,Schemaname)
SELECT sys.objects.NAME AS TABLENAME,
SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
from sys.objects
INNER JOIN sys.indexes ON
sys.objects.object_id = sys.indexes.object_id
and sys.indexes.type_desc!='HEAP'
GROUP BY sys.objects.name,
SCHEMA_NAME(sys.objects.SCHEMA_ID),sys.objects.type
HAVING (sys.objects.type='U') and
sys.objects.name<>'sysdiagrams'
order by sys.objects.name --and sys.objects.name='Test1'
---------------------------------
SET @vNumDBs = @@RowCount
SET @vCount = 1
While @vCount <= @vNumDBs
BEGIN
--Get SchemaName and Object Name for Each Object--
SELECT @SchemaName=Schemaname,@TableName=TableName
FROM @Object_Holder
WHERE TabID=@vCount
--Print '['+@SchemaName+'].['+@TableName+']'
SELECT @ConstraintName= name FROM sys.indexes
WHERE is_primary_key=1
and is_unique in (0,1)
and object_id=object_id(@TableName)
--Print @vCount
If @ConstraintName<>''
BEGIN
Print 'ALTER TABLE['+@SchemaName+'].['+@TableName+']
DROP CONSTRAINT ['+@ConstraintName+']'
--SELECT @ConstraintName
END
SET @ConstraintName=''
SET @vCount = @vCount + 1
END
SET NOCOUNT OFF

Script Indexes SQL Server 2005 & 2008

Download Updated Script : Index_Script
This script will help DBA's in scripting out all the existing
indexes(Clustered , Non-Clustered, Clustered with PK,
Clustered with Unique key, Non-Clustered with Unique Key etc.)
in a Database.Script will script the Partition Indexes which
exists on Partition Scheme,Primary Filegroup or on any Filegroup
In SQL Server 2008 there is no way to script a index with
Filegroup or Partition Scheme Name.
It seems to me as a BUG
as In SQL Server 2005 we can script the Index with Filegroup
and Partition Scheme Name.
Hope it will help lot of DBA's.
--------------------------------------------------------------
/*
Code Developed By      : Mohd Sufian
Code Developed Date    : 14th Novermber 2009
Code Developed Country : India
*/
SET NOCOUNT ON
---------------------------------------------------------------
      DECLARE @vNumDBs    Int 
      DECLARE @vCount     Int
--Decalared Variable Index Info-----------------------
      DECLARE @SchemaName           Varchar(MAX)
      DECLARE @TableName            Varchar(MAX)
      DECLARE @IndexName            Varchar(MAX)
      DECLARE @IndexType            Varchar(MAX)
      DECLARE @Index_Id             Varchar(MAX)
      DECLARE @Is_Primary_Key       INT
      DECLARE @Is_Unique_Key        INT
      DECLARE @Data_Space_id        INT
--Declared Variable Included Column In Index----------
      DECLARE @ColName              VARCHAR(max)
      DECLARE @Index_Column_id      INT
      DECLARE @KeyOrdinalid         INT
      DECLARE @partition_ordinal    INT
      DECLARE @IsDescendingKey      INT
      DECLARE @ColIncludedInPartitionFucntion VARCHAR(MAX)
-------------------------------------------------------
--Declare Storage Variable-----------------------------
      DECLARE @Rowcount INT
      DECLARE @Storage  INT
      DECLARE @IndexonFileGroup VARCHAR(MAX)
-------------------------------------------------------
--Declare Misleneous variables-------------------------
      DECLARE @CommaSeprator VARCHAR(1)
-------------------------------------------------------
DECLARE @Object_Holder TABLE (TabID int IDENTITY(1,1) ,
TableName varchar(max),Schemaname varchar(max))
INSERT INTO @Object_Holder(TableName,Schemaname)
SELECT sys.objects.NAME AS TABLENAME,
SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
from sys.objects
INNER JOIN   sys.indexes ON
sys.objects.object_id = sys.indexes.object_id
and sys.indexes.type_desc!='HEAP'
GROUP BY sys.objects.name,
SCHEMA_NAME(sys.objects.SCHEMA_ID),sys.objects.type
HAVING (sys.objects.type='U') and sys.objects.name<>'sysdiagrams'
order by sys.objects.name --and sys.objects.name='Test1'
SET @vNumDBs = @@RowCount 
SET @vCount = 1
While @vCount <= @vNumDBs 
BEGIN
SELECT @SchemaName=Schemaname,@TableName=TableName
FROM @Object_Holder where TabID=@vCount
      ---Check for Indexes on Each Objects
      DECLARE @vNumIndex            Int 
      DECLARE @vCountIndex          Int

      Print '--Index Script for Object :::::'+@TableName
      CREATE Table #Index_Info_Holder (Index_RowID INT IDENTITY(1,1),
      Index_Name varchar(MAX),Index_Type varchar(MAX),Index_Id Int,
      ObjectID INT,IsPrimaryKey INT,IsUnique INT,data_space_id INT)
      INSERT INTO #Index_Info_Holder (Index_Name,Index_Type,Index_Id,
      ObjectID,IsPrimaryKey,IsUnique,data_space_id)
      SELECT name , type_desc ,index_id,object_id,is_primary_key,
      is_unique,data_space_id FROM sys.indexes where
      object_id=OBJECT_ID(@TableName) and type_desc!='HEAP'
      --'CDS_BreakMaster')--(@TableName)
      SET @vNumIndex = @@RowCount 
      SET @vCountIndex = 1
            WHILE  @vCountIndex <= @vNumIndex
      BEGIN
      SELECT @IndexName=Index_name ,@IndexType= Index_type ,
      @Index_Id=index_id,@Is_Primary_Key=IsPrimaryKey,
      @Is_Unique_Key=IsUnique,@Data_Space_id=data_space_id
      FROM #Index_Info_Holder
      where objectid=OBJECT_ID(@TableName)
      and Index_RowID=@vCountIndex
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=1 --OR
      @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
                         'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
      Print '('
      END
      If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] '
                           + 'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE NONCLUSTERED '
      Print '('
      END
      If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
      BEGIN
      Print 'CREATE NONCLUSTERED INDEX ['+@IndexName+'] ON' + ' [' + @SchemaName + '].['
                                         + @TableName  + ']'
      Print '('
      END
      If @IndexType='CLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
      BEGIN
      Print 'CREATE CLUSTERED INDEX ['+ @IndexName +'] ON' + ' [' + @SchemaName + '].['
                                      + @TableName + ']'
      Print '('
      END
      ---Columns Included in Index--
      DECLARE @vNumIndexIncludedCol       Int 
      DECLARE @vCountIndexIncludedCol     Int
      CREATE TABLE  #Index_IncludedColumnInfo
      (
      Index_IncludedColumnRowID                   INT IDENTITY(1,1),
      Index_IncludedObjectId                      INT,
      Index_IncludedColName                       Varchar(MAX),
      Index_IncludedColID                         INT,
      Index_IncludedColKeyOrdinal                 INT,
      Index_IncludedColPartitionOrdinal           INT,
      Index_IncludedColPartitionIsDescendingKey   INT
      )   
      INSERT INTO #Index_IncludedColumnInfo
      (Index_IncludedObjectId,Index_IncludedColName,Index_IncludedColID,
       Index_IncludedColKeyOrdinal,Index_IncludedColPartitionOrdinal,
       Index_IncludedColPartitionIsDescendingKey)SELECT object_id,
       COL_NAME(object_id(@TableName),column_id),index_column_id,
       key_ordinal,partition_ordinal,is_descending_key FROM
       sys.index_columns where Object_Id=object_id(@TableName)|
      and index_id=@Index_Id and key_ordinal<>0--and Partition_ordinal!=1  
      SET @vNumIndexIncludedCol = @@RowCount 
      SET @vCountIndexIncludedCol = 1
      WHILE  @vCountIndexIncludedCol  <= @vNumIndexIncludedCol
      BEGIN
      SELECT @ColName=Index_IncludedColName,@Index_Column_id=Index_IncludedColID,
      @KeyOrdinalid=Index_IncludedColKeyOrdinal,
      @partition_ordinal=Index_IncludedColPartitionOrdinal,
      @IsDescendingKey=Index_IncludedColPartitionIsDescendingKey

      FROM #Index_IncludedColumnInfo WHERE
      Index_IncludedColumnRowID=@vCountIndexIncludedCol
      and Index_IncludedColKeyOrdinal<>0
            If @vCountIndexIncludedCol=@vNumIndexIncludedCol
            --or @vCountIndexIncludedCol  != @vNumIndexIncludedCol
                  BEGIN
                  SELECT @CommaSeprator=' '
            END
            If @vCountIndexIncludedCol<>@vNumIndexIncludedCol
            --and @vCountIndexIncludedCol  != @vCountIndexIncludedCol
                  BEGIN
                        SELECT @CommaSeprator=','
                  END 
      If @IsDescendingKey=0
            BEGIN
            Print '['+@ColName+'] ASC' + @CommaSeprator
            END
      If @IsDescendingKey=1
            BEGIN
            Print '['+@ColName+'] DESC'
            END
      SET @ColName=''
      SET @vCountIndexIncludedCol = @vCountIndexIncludedCol + 1
      END
      SELECT @ColIncludedInPartitionFucntion=COL_NAME(object_id(@TableName),column_id)
      FROM sys.index_columns where Object_Id=object_id(@TableName) and index_id=@Index_Id
      and Partition_ordinal=1   
      SELECT @Storage= Index_IncludedColPartitionOrdinal from #Index_IncludedColumnInfo
      where Index_IncludedColPartitionOrdinal>0
      Print ')'
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=0 and  @Is_Primary_Key=0
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=1
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
    If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
    If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
    BEGIN
    PRINT 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF,
               IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
               ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70)'
      END
      SELECT @IndexonFileGroup=[name] FROM SYS.DATA_SPACES WHERE data_space_id=@Data_Space_id
    If @ColIncludedInPartitionFucntion IS NULL
         BEGIN
         SET @ColIncludedInPartitionFucntion=' '
         END
       If @ColIncludedInPartitionFucntion =''--IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'
        --+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
       If @ColIncludedInPartitionFucntion !=''
          and @IndexonFileGroup<>'Primary'-- is not null --IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
       If @ColIncludedInPartitionFucntion !=''
          and @IndexonFileGroup='Primary'-- is not null --IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'--+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
      SET @Storage=''
      DROP TABLE #Index_IncludedColumnInfo
      Print '---------End of Index Script------------------------'
      SET @vCountIndex = @vCountIndex + 1     
      END
      DROP TABLE #Index_Info_Holder
      --**********************************--
 SET @vCount = @vCount + 1 
END
SET NOCOUNT OFF
Note : The script will  Generate the create script for Constraints and Indexes.