QL Server Default Constraints problem (Manipulation)

sql-server-dbconstraint.sql

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON; 
ALTER DATABASE <dbname> SET MULTI_USER; 
================================================================================================================
					SQL Server Default Constraints problem (Manipulation)
================================================================================================================

SELECT * FROM dbo.sysobjects where id > 98 order by name where name = 'ACT_FK_MODEL_SOURCE';
SELECT * FROM dbo.sysobjects where id > 98  and name = 'ACT_FK_MODEL_SOURCE';
================================================
CREATE PROCEDURE [dbo].[spDropColumnAndConstraints] 
@TableName VARCHAR(100), 
@ColumnName VARCHAR(100) AS 
BEGIN
 DECLARE @sql NVARCHAR(MAX)
 SET @sql = CAST(
		( SELECT 'ALTER TABLE ' + OBJECT_NAME(C.id) + ' DROP CONSTRAINT ' + OBJECT_NAME(C.constid) FROM sysobjects C 
				INNER JOIN sys.columns SC ON C.id = SC.object_id AND C.colid = SC.column_id 
			WHERE C.id = OBJECT_ID(@TableName) AND SC.name = @ColumnName FOR XML PATH('') 
		) 
		AS NVARCHAR(MAX)
	) 
	' ALTER TABLE ' + @TableName  +' DROP COLUMN '+ @ColumnName 
 EXEC (@sql) 
END ;
 
  select * from sysobjects
 select OBJECT_NAME(SC.object_id)   from sys.columns SC
 
 SELECT   OBJECT_NAME(C.constid) FROM sysobjects C 
				INNER JOIN sys.columns SC ON C.id = SC.object_id AND C.colid = SC.column_id 

				
-- list all DEFAULT constraints
	
SELECT 'EXEC sp_rename ''' + QUOTENAME(OBJECT_SCHEMA_NAME(dc.parent_object_id)) + '.'
+ QUOTENAME(dc.name) + ''', ''DF_' + OBJECT_NAME(dc.parent_object_id) + '_' + c.name + ''''
FROM sys.default_constraints dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE dc.name <> 'DF_' + OBJECT_NAME(dc.parent_object_id) + '_' + c.name;
--------------------------------------------------------------------------------------------------------
-- List all PRIMARY_CONSTRAINTS
;WITH PKNames
AS (
SELECT name AS IndexName
,OBJECT_NAME(object_id) AS TableName
,OBJECT_SCHEMA_NAME(object_id) as SchemaName
,(SELECT '' + c.name
FROM sys.index_columns ic
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.index_column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')) AS Columns
FROM sys.indexes i
WHERE i.is_primary_key = 1
)
SELECT 'EXEC sp_rename ''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(IndexName) + ''', ''PK_' + TableName + '_' + Columns + ''''
FROM PKNames
WHERE IndexName <> 'PK_' + TableName + '_' + Columns;
-------------------------------------------------------------------------------------------------------------------------------
-- RENAMES PRIMARY CONSTRAINTS
DECLARE @pk SYSNAME SELECT @pk = name FROM sysobjects WHERE parent_obj
= object_id('<table_name>') AND name LIKE 'PK%' AND xtype = 'PK'
EXEC sp_rename @pk, 'PK_<table_name>'