Skip to content
  • Home
  • SQL Server
    • SQL Server Events
    • T-SQL Tips and Tricks
    • Issues/Troubleshooting
    • SSIS
    • SSRS
    • SQLXML
  • PowerShell
  • Tableau
    • Data Sets
    • Tableau for Teaching
    • Tableau Tips
    • UBC Tableau User Group
  • Power BI
    • Data Sets
  • Books
  • About
    • How I Got Here
    • Teaching
    • Articles
    • Presentations
    • Random Musings

sqlbelle

tutorials, tips, tricks, stories about my data adventures (and mis-adventures!)

Metadata

Script to Drop/Recreate CHECK Constraints

October 4, 2008November 9, 2014belle1 Comment
   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))

Share this:

  • LinkedIn
  • Twitter
  • Facebook
  • Pocket
  • Pinterest
  • Reddit
  • Tumblr
  • Print

Like this:

Like Loading...
DBA Toolbox / T-SQL Scripts, SQL Server, T-SQL Tips and Tricksconstraints, Metadata, Sample Code

Script to Drop/Recreate Default Constraints

October 4, 2008November 9, 2014belle3 Comments
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]

Share this:

  • LinkedIn
  • Twitter
  • Facebook
  • Pocket
  • Pinterest
  • Reddit
  • Tumblr
  • Print

Like this:

Like Loading...
DBA Toolbox / T-SQL Scripts, SQL Server, T-SQL Tips and TricksMetadata

Script to Drop/Recreate Unique Indexes

October 4, 2008November 9, 2014belleLeave a comment

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)

Share this:

  • LinkedIn
  • Twitter
  • Facebook
  • Pocket
  • Pinterest
  • Reddit
  • Tumblr
  • Print

Like this:

Like Loading...
DBA Toolbox / T-SQL Scripts, SQL ServerMetadata

Connect

YouTube
sqlbelle's data adventures

Twitter:
@sqlbelle

LinkedIn:
Donabel Santos

15 charts in Tableau

https://www.youtube.com/watch?v=aHVWPYu81JU

Follow me on Twitter

My Tweets
Amazon Author - Donabel Santos
Microsoft SQL Server MVP
Microsoft Certified Trainer
Tableau Desktop 8 Certified

Tags

.NET Analytics Ebook Metadata Misc Scripts PASS PASSWIT Sample Code SharePoint SMO Speaking Engagements SQL Reporting Services SQL Server SQL Server Powershell SQLXML SQLXML How To SSIS SSRS T-SQL T-SQL Tips and Tricks Tableau Tableau Certification Tableau Tips Tools troubleshooting Visualization vmware Women In Technology XML XSD

Recent Posts

  • sqlbelle’s data adventures (Tableau/SQL Server tutorials on YouTube)
  • Getting maximum consecutive years in T-SQL using Common Table Expressions (CTE)
  • More reasons why I teach
  • Automate SSRS Report Generation using PowerShell
  • Windows 10 and Visual Studio 2015 Jumpstart and Resources
  • Discovering SSRS Report Parameters using PowerShell
  • Conversations with Impact – the importance of stories
  • Upcoming SQL Server Events – SQL Saturday #407 Vancouver and 24HOP

Blog Archives

RSS Brent Ozar

  • Free Webcast: Planning a SQL Server to Azure Migration in 2021
  • New SQL ConstantCare® Feature: One Serious Query to Tune
  • Updated First Responder Kit and Consultant Toolkit for February 2021
  • How to Trace a Nested Stored Procedure Without Using Profiler

RSS Simple-Talk

  • Business value of lean software development and DevOps
  • Do something that scares you
  • Integrate Create React app with .NET Core 5
  • A data transformation problem in SQL and Scala: Dovetailing declarative solutions
  • SQL Server authentication methods, logins, and database users

RSS MSSQLTips

  • Favorite Features of SSIS
  • Azure AD Authentication for Azure SQL Databases
  • Reading a specific file from a list of objects stored in AWS S3 bucket using Python
  • Create Calendar Table Using Power Query M Language
  • How to Install and Configure SSRS with Amazon RDS SQL Server

RSS Database Journal

  • Overview: Six Backup Options for SAP HANA on Azure VMs
  • Tip 74 – Changing Cost Threshold for Parallelism
  • Is COUNT(rowid) Faster Than COUNT(*)?
  • How Many Databases Can You Name?
  • How to Find the Estimation Cost for a Query
Blog at WordPress.com.
%d bloggers like this: