EXEC SP_MSFOREACHTABLE N' ALTER TABLE ? ADD ID INT IDENTITY(1,1) '
EXEC SP_MSFOREACHTABLE N' ALTER TABLE ? ADD ID INT PRIMARY KEY IDENTITY(1,1) '
declare @s nvarchar(max) select @s=isnull(@s+'','')+' alter table '+name+' add ID int identity primary key' from sysobjects where type='U' exec(@s)
可否通过sysobjects对所有表添加呢
use master go if exists(select * from sysdatabases where name='mytest') drop database mytest go create database mytest go use mytest go create table user1 ( mid int , name varchar(25) ) go create table user2 ( mid int, name varchar(25) ) goselect * from user1 select * from user2 --drop table user1 --drop table user2--*****start*****-- DECLARE @table_name sysname --保存表名的全局变量 -- 获得所有User表 DECLARE tables_cursor CURSOR FOR select name from sysobjects where xtype='U' and status>0 --(加入这个条件是因为前面的条件查询出来的结果带有"dtproperties"非用户所建的表,应该还有其他滤去该表的条件) OPEN tables_cursor -- Perform the first fetch FETCH NEXT FROM tables_cursor INTO @table_name -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- 遍历表@table_name的每条记录 -- 动态生成执行语句 exec ( 'alter table '+ @table_name+' drop column mid ' ) exec ('alter table '+ @table_name +' add mid int primary key IDENTITY(1,1) ') FETCH NEXT FROM tables_cursor INTO @table_name END CLOSE tables_cursor --CLOSE语句关闭已打开的游标,之后不能对游标进行读取等操作,但可以使用OPEN语句再次打开该游标 DEALLOCATE tables_cursor --DEALLOCATE语句删除定义游标的数据结构,删除后不可再用
--没主键的写入表backup_table SELECT a.name,b.name into backup_table FROM sysobjects as a,syscolumns as b where b.status=128 and a.id=b.id and a.name in ( select name from sysobjects s where xtype = 'U' AND OBJECTPROPERTY(s.id , 'TableHasIdentity' ) = 1 AND OBJECTPROPERTY(s.id , 'TableHasPrimaryKey' ) = 0 )--添加主键 declare @name varchar(50),@cloumm varchar(50),@sql varchar(200) while (select count(*) from backup_table)>0 begin select top 1 @name=name,@cloumm=cloumn from backup_table exec('alter table ' +@name +' add CONSTRAINT ' +'PK_'+@name+ ' PRIMARY KEY CLUSTERED('+@cloumm+')') delete from backup_table where name=@name end
use kisscard go--*****start*****-- DECLARE @table_name sysname --保存表名的全局变量 -- 获得所有User表 DECLARE tables_cursor CURSOR FOR select name from sysobjects where xtype='U' and status>0 --(加入这个条件是因为前面的条件查询出来的结果带有"dtproperties"非用户所建的表,应该还有其他滤去该表的条件) OPEN tables_cursor -- Perform the first fetch FETCH NEXT FROM tables_cursor INTO @table_name -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- 遍历表@table_name的每条记录 -- 动态生成执行语句 exec ('alter table '+ @table_name +' change id int primary key IDENTITY(1,1) ') FETCH NEXT FROM tables_cursor INTO @table_name END CLOSE tables_cursor --CLOSE语句关闭已打开的游标,之后不能对游标进行读取等操作,但可以使用OPEN语句再次打开该游标 DEALLOCATE tables_cursor --DEALLOCATE语句删除定义游标的数据结构,删除后不可再用这样就行了
以前发过一个帖子.问 alter table abc alter column id int identity 的问题. 这种方法不行.要用语句实现很麻烦.或drop 再 add id int identity not null 还是用面板点几下来实现较好.
select @s=isnull(@s+'','')+' alter table '+name+' add ID int identity primary key' from sysobjects
where type='U'
exec(@s)
go
if exists(select * from sysdatabases where name='mytest')
drop database mytest
go
create database mytest
go
use mytest
go
create table user1
(
mid int ,
name varchar(25)
)
go
create table user2
(
mid int,
name varchar(25)
)
goselect * from user1
select * from user2
--drop table user1
--drop table user2--*****start*****--
DECLARE @table_name sysname --保存表名的全局变量
-- 获得所有User表
DECLARE tables_cursor CURSOR FOR select name from sysobjects where xtype='U' and status>0 --(加入这个条件是因为前面的条件查询出来的结果带有"dtproperties"非用户所建的表,应该还有其他滤去该表的条件)
OPEN tables_cursor
-- Perform the first fetch
FETCH NEXT FROM tables_cursor INTO @table_name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- 遍历表@table_name的每条记录
-- 动态生成执行语句
exec ( 'alter table '+ @table_name+' drop column mid ' )
exec ('alter table '+ @table_name +' add mid int primary key IDENTITY(1,1) ')
FETCH NEXT FROM tables_cursor INTO @table_name
END
CLOSE tables_cursor --CLOSE语句关闭已打开的游标,之后不能对游标进行读取等操作,但可以使用OPEN语句再次打开该游标
DEALLOCATE tables_cursor --DEALLOCATE语句删除定义游标的数据结构,删除后不可再用
SELECT a.name,b.name into backup_table FROM sysobjects as a,syscolumns as b where b.status=128 and a.id=b.id
and a.name in
(
select name from sysobjects s where xtype = 'U'
AND OBJECTPROPERTY(s.id , 'TableHasIdentity' ) = 1
AND OBJECTPROPERTY(s.id , 'TableHasPrimaryKey' ) = 0
)--添加主键
declare @name varchar(50),@cloumm varchar(50),@sql varchar(200)
while (select count(*) from backup_table)>0
begin
select top 1 @name=name,@cloumm=cloumn from backup_table
exec('alter table ' +@name +' add CONSTRAINT ' +'PK_'+@name+ ' PRIMARY KEY CLUSTERED('+@cloumm+')')
delete from backup_table where name=@name
end
go--*****start*****--
DECLARE @table_name sysname --保存表名的全局变量
-- 获得所有User表
DECLARE tables_cursor CURSOR FOR select name from sysobjects where xtype='U' and status>0 --(加入这个条件是因为前面的条件查询出来的结果带有"dtproperties"非用户所建的表,应该还有其他滤去该表的条件)
OPEN tables_cursor
-- Perform the first fetch
FETCH NEXT FROM tables_cursor INTO @table_name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- 遍历表@table_name的每条记录
-- 动态生成执行语句
exec ('alter table '+ @table_name +' change id int primary key IDENTITY(1,1) ')
FETCH NEXT FROM tables_cursor INTO @table_name
END
CLOSE tables_cursor --CLOSE语句关闭已打开的游标,之后不能对游标进行读取等操作,但可以使用OPEN语句再次打开该游标
DEALLOCATE tables_cursor --DEALLOCATE语句删除定义游标的数据结构,删除后不可再用这样就行了
这种方法不行.要用语句实现很麻烦.或drop 再 add id int identity not null
还是用面板点几下来实现较好.