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)