belle’s sql musings

Script to Drop/Recreate CHECK Constraints

Posted in DBA Toolbox / T-SQL Scripts by belle on October 4, 2008 
   1: -- ===========================================================
   2: -- Check Constraints
   3: -- How to script out Check Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- Drop Check Constraints
   8: SELECT
   9:     'ALTER TABLE  ' +
  10:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(CONSTRAINT_NAME)
  14: FROM
  15:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  16:     INNER JOIN sys.sysobjects so
  17:     ON cc.CONSTRAINT_NAME = so.[name]
  18:
  19: -- Recreate Check Constraints
  20: SELECT
  21:     'ALTER TABLE  ' +
  22:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  23:     CHAR(10) +
  24:     ' ADD CONSTRAINT ' +
  25:     QuoteName(CONSTRAINT_NAME) +
  26:     ' CHECK ' +
  27:     CHECK_CLAUSE
  28: FROM
  29:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  30:     INNER JOIN sys.sysobjects so
  31:     ON cc.CONSTRAINT_NAME = so.[name]

Here is a sample result:

ALTER TABLE  [ProductReview]
 DROP CONSTRAINT [CK_ProductReview_Rating]
ALTER TABLE  [ProductReview]
 ADD CONSTRAINT [CK_ProductReview_Rating] CHECK ([Rating]>=(1) AND [Rating]<=(5))
Tagged with: , ,

leave a comment

Script to Drop/Recreate Default Constraints

Posted in DBA Toolbox / T-SQL Scripts by belle on October 4, 2008
Here is a basic script to drop and recreate default constraints in SQL Server.
   1: -- ===========================================================
   2: -- Default Constraints
   3: -- How to script out Default Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- drop default constraints
   8: SELECT
   9:     'ALTER TABLE ' +
  10:     QuoteName(OBJECT_NAME(sc.id)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(OBJECT_NAME(sc.cdefault))
  14: FROM
  15:     syscolumns sc
  16:     INNER JOIN
  17:     sysobjects as so on sc.cdefault = so.id
  18:     INNER JOIN
  19:     syscomments as sm on sc.cdefault = sm.id
  20: WHERE
  21:     OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1
  22:
  23: -- create default constraints
  24: SELECT
  25:     'ALTER TABLE ' +
  26:     QuoteName(OBJECT_NAME(sc.id)) +
  27:     'WITH NOCHECK ADD CONSTRAINT ' +
  28:     QuoteName(OBJECT_NAME(sc.cdefault))+
  29:     ' DEFAULT ' +
  30:     sm.text +
  31:     ' FOR ' + QuoteName(sc.name)
  32:     + CHAR(13)+CHAR(10)
  33: FROM
  34:     syscolumns sc
  35:     INNER JOIN
  36:     sysobjects as so on sc.cdefault = so.id
  37:     INNER JOIN
  38:     syscomments as sm on sc.cdefault = sm.id
  39: WHERE
  40:     OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1
Here is a sample result:
ALTER TABLE [SalesOrderHeader]
 DROP CONSTRAINT [DF_SalesOrderHeader_Status]

ALTER TABLE [PurchaseOrderHeader] WITH NOCHECK
 ADD CONSTRAINT [DF_PurchaseOrderHeader_Status] DEFAULT ((1)) FOR [Status]
Tagged with:

leave a comment

Script to Drop/Recreate Unique Indexes

Posted in DBA Toolbox / T-SQL Scripts by belle on October 4, 2008

Here’s a basic script to drop/recreate unique indexes in SQL Server 2005.  This includes scripting out INCLUDED columns.

   1: -- ===========================================================
   2: -- Unique Indexes 
   3: -- How to script out Unique Indexes in SQL Server 2005
   4: -- Set Results to Text so you can copy and paste the result
   5: -- ===========================================================
   6: SET NOCOUNT ON
   7:
   8: -- Drop Unique Indexes
   9: SELECT
  10:     DISTINCT
  11:     ' DROP INDEX ' +
  12:     QuoteName(i.name) +
  13:     ' ON ' +
  14:     QuoteName(OBJECT_NAME(i.object_id))
  15: FROM
  16:     sys.index_columns cc
  17:     INNER JOIN sys.indexes i ON cc.object_id = i.object_id
  18:     AND cc.index_id = i.index_id
  19:     INNER JOIN sys.objects so
  20:     ON i.object_id = so.object_id
  21: WHERE
  22:     is_primary_key = 0 AND
  23:     is_unique = 1 AND
  24:     so.type = 'U'
  25:
  26: -- Recreate Unique Indexes
  27: SELECT
  28:     DISTINCT
  29:     'CREATE UNIQUE ' +
  30:     CASE OBJECTPROPERTY(so.object_id, N'CnstIsClustKey')
  31:     WHEN 1 THEN 'CLUSTERED '
  32:     ELSE ''
  33:     END +
  34:     ' INDEX ' +
  35:     QuoteName(i.name) +
  36:     ' ON ' +
  37:     QuoteName(OBJECT_NAME(i.object_id))+
  38:     '('+ LEFT(UniqueCols.col, LEN(UniqueCols.col) -1)
  39:     +')' +
  40:     CASE ISNULL(LEN(IncludedCols.col), 0)
  41:         WHEN 0 THEN ''
  42:         ELSE ' INCLUDE (' + LEFT(IncludedCols.col, LEN(IncludedCols.col) -1) + ')'
  43:     END
  44: FROM
  45:     sys.index_columns cc
  46:     INNER JOIN sys.indexes i ON cc.object_id = i.object_id
  47:     AND cc.index_id = i.index_id
  48:     INNER JOIN sys.objects so
  49:     ON i.object_id = so.object_id
  50: CROSS APPLY
  51: (
  52:     SELECT
  53:         sc.name + ','
  54:     FROM
  55:         sys.index_columns idxcol
  56:         INNER JOIN sys.columns sc
  57:         ON idxcol.column_id=sc.column_id
  58:         AND idxcol.object_id=sc.object_id
  59:     WHERE
  60:         idxcol.object_id = i.object_id
  61:         AND i.index_id = idxcol.index_id
  62:         AND is_included_column = 0
  63:     FOR XML PATH('')
  64: )UniqueCols(col)
  65: CROSS APPLY
  66: (
  67:     SELECT
  68:         sc.name + ','
  69:     FROM
  70:         sys.index_columns idxcol
  71:         INNER JOIN sys.columns sc
  72:         ON idxcol.column_id=sc.column_id
  73:         AND idxcol.object_id=sc.object_id
  74:     WHERE
  75:         idxcol.object_id = i.object_id
  76:         AND i.index_id = idxcol.index_id
  77:         AND is_included_column = 1
  78:     FOR XML PATH('')
  79: )IncludedCols(col)
  80: WHERE
  81:     is_primary_key = 0 AND
  82:     is_unique = 1 AND
  83:     so.type = 'U'
  84:
  85: SET NOCOUNT OFF

Here is a sample result:

DROP INDEX [test_idx] ON [CompanyDepartment]
DROP INDEX [test_idx2] ON [Customer]

CREATE UNIQUE  INDEX [test_idx] ON [CompanyDepartment](Name,GroupName) INCLUDE (ModifiedDate)
CREATE UNIQUE  INDEX [test_idx2] ON [Customer](FirstName,MiddleName,CustomerID)
Tagged with:

leave a comment