这个可以查,但是你这样删除主键甚至字段貌似不好,还不如重新建表导入数据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
sys.sysconstraints status int Pseudo-bit-mask indicating the status. Possible values include the following:1 = PRIMARY KEY constraint 2 = UNIQUE KEY constraint 3 = FOREIGN KEY constraint 4 = CHECK constraint 5 = DEFAULT constraint 16 = Column-level constraint 32 = Table-level constraint
SELECT TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END, TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''), Column_id=C.column_id, ColumnName=C.name, PrimaryKey=ISNULL(IDX.PrimaryKey,N''), [IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END, Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, Type=T.name, Length=C.max_length, Precision=C.precision, Scale=C.scale, NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, [Default]=ISNULL(D.definition,N''), ColumnDesc=ISNULL(PFD.[value],N''), IndexName=ISNULL(IDX.IndexName,N''), IndexSort=ISNULL(IDX.Sort,N''), Create_Date=O.Create_Date, Modify_Date=O.Modify_date FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T ON C.user_type_id=T.user_type_id LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.[object_id] LEFT JOIN sys.extended_properties PFD ON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id -- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0 AND C.[object_id]=PTB.major_id -- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) LEFT JOIN -- 索引及主键信息 ( SELECT IDXC.[object_id], IDXC.column_id, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, IndexName=IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 ( SELECT [object_id], Column_id, index_id=MIN(index_id) FROM sys.index_columns GROUP BY [object_id], Column_id ) IDXCUQ ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id ) IDX ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id 以前的帖子~你试试......把主键字段都删了
现在有这要求所以要想个语句方法出来试试这个:declare @sql nvarchar(4000) declare @tb_name nvarchar(30)set @sql = '' set @tb_name = '表名'--先删除主键 select @sql = @sql + 'alter table ['+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 @sql = @sql + 'alter table ['+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(@sql)
屌丝新人 不会给我这么复杂的任务的。呵呵,没必要这么复杂,你试试我上面的那个 这个是怎么运行的 结果是完成命令 但是表中主键还存在这个代码只是查找主键,不会删除主键,试试下面的代码:declare @sql nvarchar(4000) declare @tb_name nvarchar(30)set @sql = '' set @tb_name = '表名'--先删除主键 select @sql = @sql + 'alter table ['+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 @sql = @sql + 'alter table ['+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(@sql)
可以用的USE tempdb GO CREATE TABLE test(id INT PRIMARY KEY ,NAME VARCHAR(10)) INSERT INTO test(id) values(1),(2)declare @sql nvarchar(4000) declare @tb_name nvarchar(30)
set @sql = '' set @tb_name = 'test'
--先删除主键 select @sql = @sql + 'alter table ['+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 @sql = @sql + 'alter table ['+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
如果表数量不限,要用变量表示表名 我做了改进但是无法删除主键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_%'declare @x int,@y int,@tb_name nvarchar(50) declare @table nvarchar(4000) set @x=1 select @y=MAX(num)from #temp select @tb_name=tbname from #temp
while @x<@y begin select @table = @table + 'alter table ['+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 + 'alter table ['+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 set @x=@x+1 end select * from #temp drop table #temp
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_%'declare @x int,@y int,@tb_name nvarchar(50) declare @table nvarchar(4000) set @x=1 select @y=MAX(num)from #temp select @tb_name=tbname from #temp where num=@x while @x<@y beginselect @table = @table + 'alter table ['+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 + 'alter table ['+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 @x=@x+1 end drop table #temp
再试试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_%'
declare @x int,@y int,@tb_name nvarchar(50) declare @table nvarchar(4000) set @x=1 select @y=MAX(num)from #temp select @tb_name=tbname from #temp where num=@x while @x<@y begin
select @table = @table + 'alter table ['+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 exec(@table) select @table = @table + 'alter table ['+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 @x=@x+1 end drop table #temp
试试这个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_%'
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 ['+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 exec(@table) select @table = @table + 'alter table ['+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 @x=@x+1 end drop table #temp
在试试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 ['+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 exec(@table) select @table = @table + 'alter table ['+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 @x=@x+1 end drop table #temp
测了一下,可以了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_%'
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 exec(@table) SET @table='' select @table = @table + 'alter table ['+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 @x=@x+1 end drop table #temp
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) DECLARE @table2 NVARCHAR(4000) SET @x = 1 SET @table = N'' SET @table2 = 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 @x = @x + 1 END DROP TABLE #temp
(3 行受影响) alter table [dbo].[tb1] drop constraint [PK__tb1__3213E83F5441852A];alter table [dbo].[tb1] drop column id; alter table [dbo].[tb1] drop constraint [PK__tb1__3213E83F5441852A];alter table [dbo].[tb1] drop column id;alter table [dbo].[tb2] drop constraint [PK__tb2__90DB479E5812160E];alter table [dbo].[tb2] drop column id;alter table [dbo].[tb2] drop column sent; alter table [dbo].[tb1] drop constraint [PK__tb1__3213E83F5441852A];alter table [dbo].[tb1] drop column id;alter table [dbo].[tb2] drop constraint [PK__tb2__90DB479E5812160E];alter table [dbo].[tb2] drop column id;alter table [dbo].[tb2] drop column sent;alter table [dbo].[tb3] drop constraint [PK__tb3__3213E83F5BE2A6F2];alter table [dbo].[tb3] drop column id;可以的
WHERE status=1
status
int
Pseudo-bit-mask indicating the status. Possible values include the following:1 = PRIMARY KEY constraint 2 = UNIQUE KEY constraint 3 = FOREIGN KEY constraint 4 = CHECK constraint 5 = DEFAULT constraint 16 = Column-level constraint 32 = Table-level constraint
SELECT
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id 以前的帖子~你试试......把主键字段都删了
declare @tb_name nvarchar(30)set @sql = ''
set @tb_name = '表名'--先删除主键
select @sql = @sql +
'alter table ['+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 @sql = @sql + 'alter table ['+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(@sql)
这个是怎么运行的 结果是完成命令 但是表中主键还存在这个代码只是查找主键,不会删除主键,试试下面的代码:declare @sql nvarchar(4000)
declare @tb_name nvarchar(30)set @sql = ''
set @tb_name = '表名'--先删除主键
select @sql = @sql +
'alter table ['+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 @sql = @sql + 'alter table ['+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(@sql)
GO
CREATE TABLE test(id INT PRIMARY KEY ,NAME VARCHAR(10))
INSERT INTO test(id) values(1),(2)declare @sql nvarchar(4000)
declare @tb_name nvarchar(30)
set @sql = ''
set @tb_name = 'test'
--先删除主键
select @sql = @sql +
'alter table ['+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 @sql = @sql + 'alter table ['+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(@sql)
1、如果有外键,你这样做可能会报错。
2、主键是用于标识数据,删除了,你的数据“可能会”混乱。
3、sqlserver主键上默认有聚集索引,而其他非聚集索引都指向这个聚集索引,删除它,会导致其他所有非聚集索引重组,对性能带来很大影响。
4、还有对应用程序的一些非预期影响。
除非用于测试,不然我不建议直接这样做,
insert into #temp select distinct table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like 'PK_%'declare @x int,@y int,@tb_name nvarchar(50)
declare @table nvarchar(4000)
set @x=1
select @y=MAX(num)from #temp
select @tb_name=tbname from #temp
while @x<@y
begin
select @table = @table +
'alter table ['+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 +
'alter table ['+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
set @x=@x+1
end
select * from #temp
drop table #temp
2、不能删除是完全不不能还是部分?有没有报错?
insert into #temp select distinct table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like 'PK_%'declare @x int,@y int,@tb_name nvarchar(50)
declare @table nvarchar(4000)
set @x=1
select @y=MAX(num)from #temp
select @tb_name=tbname from #temp where num=@x
while @x<@y
beginselect @table = @table +
'alter table ['+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 +
'alter table ['+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 @x=@x+1
end
drop table #temp
insert into #temp select distinct table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like 'PK_%'
declare @x int,@y int,@tb_name nvarchar(50)
declare @table nvarchar(4000)
set @x=1
select @y=MAX(num)from #temp
select @tb_name=tbname from #temp where num=@x
while @x<@y
begin
select @table = @table +
'alter table ['+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
exec(@table)
select @table = @table +
'alter table ['+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 @x=@x+1
end
drop table #temp
insert into #temp select distinct table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like 'PK_%'
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 ['+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
exec(@table)
select @table = @table +
'alter table ['+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 @x=@x+1
end
drop table #temp
消息 3728,级别 16,状态 1,第 1 行
'PK__tb1__3213E83F20C1E124' 不是约束。
消息 3727,级别 16,状态 0,第 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 ['+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
exec(@table)
select @table = @table +
'alter table ['+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 @x=@x+1
end
drop table #temp
insert into #temp select distinct table_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like 'PK_%'
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
exec(@table)
SET @table=''
select @table = @table +
'alter table ['+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 @x=@x+1
end
drop 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)
DECLARE @table2 NVARCHAR(4000)
SET @x = 1
SET @table = N''
SET @table2 = 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 @x = @x + 1
END
DROP TABLE #temp
'PK__tb1__3213E83F48CFD27E' 不是约束。
消息 3727,级别 16,状态 0,第 1 行
未能删除约束。请参阅前面的错误信息。
同样 可以删除主键和列但是报错而且只能删除第一个表的
alter table [dbo].[tb1] drop constraint [PK__tb1__3213E83F5441852A];alter table [dbo].[tb1] drop column id;
alter table [dbo].[tb1] drop constraint [PK__tb1__3213E83F5441852A];alter table [dbo].[tb1] drop column id;alter table [dbo].[tb2] drop constraint [PK__tb2__90DB479E5812160E];alter table [dbo].[tb2] drop column id;alter table [dbo].[tb2] drop column sent;
alter table [dbo].[tb1] drop constraint [PK__tb1__3213E83F5441852A];alter table [dbo].[tb1] drop column id;alter table [dbo].[tb2] drop constraint [PK__tb2__90DB479E5812160E];alter table [dbo].[tb2] drop column id;alter table [dbo].[tb2] drop column sent;alter table [dbo].[tb3] drop constraint [PK__tb3__3213E83F5BE2A6F2];alter table [dbo].[tb3] drop column id;可以的