|
sqlserver判断一张表的某个字段是否是主键判断一张表的某个字段是否是主键，如果有则删除主键，并删除该字段
SELECT OBJECT_NAME(id)[tablename],col.name FROM sys.sysconstraints cons LEFT JOIN sys.columns col ON cons.colid=col.column_id WHERE status=1
CREATE TABLE #temp
(
tbname NVARCHAR(50) ,
num INT IDENTITY(1, 1)
)
INSERT INTO #temp
SELECT DISTINCT
table_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE 'PK_%'
AND TABLE_NAME NOT LIKE '%#%'
DECLARE @x INT ,
@y INT ,
@tb_name NVARCHAR(50)
DECLARE @table NVARCHAR(4000)
SET @x = 1
SET @table = N''
SELECT @y = MAX(num)
FROM #temp
WHILE @x <= @y
BEGIN
SELECT @tb_name = tbname
FROM #temp
WHERE num = @x
SELECT @table = @table + 'alter table [dbo].[' + t.name
+ '] drop constraint [' + i.name + '];'
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
WHERE i.is_primary_key = 1
AND t.name = @tb_name
SELECT @table =@table+ N'alter table [dbo].[' + t.name + '] drop column '
+ c.name + ';'
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
WHERE i.is_primary_key = 1
AND t.name = @tb_name
exec ( @table )
SET @table=N''
SET @x = @x + 1
END
DROP TABLE #temp
| 