Technical Article

Default Constraints with PRINT or DROP

,

To Retreive List of Default Constraints from current database with PRINT or DROP

/****** Object:  StoredProcedure [dbo].[usp_getDefaultConstraints]    Script Date: 04/20/2007 23:06:04 ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_getDefaultConstraints]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_getDefaultConstraints]
GO
/*
Purpose : To Retreive List of Default Constraints from current database
Created  Date: 04/20/2007
Created  by: Satyanarayana Bommidi
Parameters: Optional Parameters
Input: @TablesName = {Table Name if Required}, @ColumnName  = {Column Name if Required} 
  and @isDrop {0 is for SELECT and 1 for DROP}
EXEC: EXEC usp_getDefaultConstraints @TablesName = 'EZCAP_CONFIG', @ColumnName = 'VLD_CONSPROV2', @isDrop = 0
*/CREATE PROCEDURE usp_getDefaultConstraints (@TablesName VARCHAR(120) = '', @ColumnName VARCHAR(120) = '', @isDrop BIT = 0)
AS
BEGIN
SELECT IDENTITY(INT, 1, 1) as Row_ID, CONVERT(VARCHAR(50), schema_name(t.schema_id)) as Schema_Name, CONVERT(VARCHAR(250), t.Name) as Table_Name, CONVERT(VARCHAR(120), c.Name) as Column_Name, CONVERT(VARCHAR(120), d.Name) as Default_Constraints_Name, CONVERT(VARCHAR(250), d.definition) as Default_Value
INTO #Defaults FROM sys.tables t
LEFT JOIN sys.default_constraints d ON t.object_id = d.parent_object_id
LEFT JOIN sys.columns c ON t.object_id = c.object_id AND d.parent_column_id = c.column_id
WHERE (@TablesName = '' OR CONVERT(VARCHAR(250), t.Name) = @TablesName) AND (@ColumnName = '' OR CONVERT(VARCHAR(250), c.Name) = @ColumnName)

DECLARE @RowID INT, @SQL VARCHAR(1000)
SELECT @RowID = 1, @SQL = ''
IF @isDrop = 1
BEGIN
SELECT @RowID = MIN(Row_ID) FROM #Defaults
WHILE @RowID <= (SELECT MAX(Row_ID) FROM #Defaults)
BEGIN
SELECT @SQL = 'ALTER TABLE ['+Schema_Name+'].['+Table_Name+'] DROP CONSTRAINT ['+Default_Constraints_Name+'] ' FROM #Defaults WHERE Row_ID = @RowID AND ISNULL(Schema_Name, '') <> '' AND ISNULL(Table_Name, '') <> '' AND ISNULL(Default_Constraints_Name, '') <> ''
PRINT @SQL 
EXEC(@SQL)
SELECT @RowID = MIN(Row_ID) FROM #Defaults WHERE Row_ID > @RowID
END
END
ELSE SELECT * FROM #Defaults
DROP TABLE #Defaults
END
GO

Rate

4 (1)

Share

Share

Rate

4 (1)