use dbxxx
declare mycursor cursor for
select name from sysobjects where xtype = 'u' and status > = 0 and name like 'sb_%'
open mycursor
declare @tb_name varchar(100)
fetch next from mycursor into @tb_name
while (@@fetch_status = 0)
begin
exec ('declare table @tb2 (....) insert into @tb2 exec sp_pkeys ' + @tb_name)
if (@@rowcount = 0)
print @tb_name
fetch next from mycursor into @tb_name
end意图就是把没有主键的表打印出来 建主键的系统SP我不知道:(
close mycursor
deallocate mycursor
--@tb2的结构就是sp_pkeys的输出结构
declare mycursor cursor for
select name from sysobjects where xtype = 'u' and status > = 0 and name like 'sb_%'
open mycursor
declare @tb_name varchar(100)
fetch next from mycursor into @tb_name
while (@@fetch_status = 0)
begin
exec ('declare table @tb2 (....) insert into @tb2 exec sp_pkeys ' + @tb_name)
if (@@rowcount = 0)
print @tb_name
fetch next from mycursor into @tb_name
end意图就是把没有主键的表打印出来 建主键的系统SP我不知道:(
close mycursor
deallocate mycursor
--@tb2的结构就是sp_pkeys的输出结构
解决方案 »
- 求一条updatesql语句。希望你们能进来帮帮我。谢谢你们了。
- 如何插入一列时间
- sql调查问卷结果统计计算问题
- 卸载SQL2000的时候会不会连数据一起删除掉?
- 数据库操作模拟网络拥堵或服务回复延迟
- 我想用VB一个连接SQL SERVER数据库,此数据库在另一台机器(服务器)上,请问如何设置数据源等?本机要不要安装SQL SERVER ?谢谢!
- 求三条SQL语句,每条20分,我实在是想不出来了.
- a表,b表同样的字段,怎么比较一些数据在a表中存在,b表中不存在。50分,在线等待。
- 求关于“对象关系数据库”的学习资料
- 今天又一问:为什么我的SQLServer需要重启一次服务后才能正常工作?
- 在ASP程序中将ACCESS直接导入到SQL SERVER 出错,求解,谢谢
- 关于DELPHI里调用存储过程的一个问题
TABLE_QUALIFIER varchar(100),
TABLE_OWNER varchar(100),
TABLE_NAME varchar(100),
COLUMN_NAME varchar(100),
KEY_SEQ smallint,
PK_NAME varchar(100)
)这里根据COLUMN_NAME也可以判断出 已经添加了主键 但不是sbbh列的表 然后再做一些处理
create table sb_x2(sbbh int constraint pk_sb_x2_sbbh primary key clustered(sbbh),col2 varchar(20))
create table sb_x3(col varchar(20),col1 varchar(30))
goselect cast(a.name as varchar(30)) as table_name,cast(b.name as varchar(30)) as primary_name from(
select id,name from sysobjects where xtype='u' and name like 'sb_%') a
left join
(
select a.id,a.name from syscolumns a where exists (
select * from sysindexkeys b where a.id=b.id and a.colid=b.colid)
) b on a.id=b.iddrop table sb_x1,sb_x2,sb_x3table_name primary_name
------------------------------ ------------------------------
sb_x1 sbbh
sb_x2 sbbh
sb_x3 NULL(所影响的行数为 3 行)
create table sb_x2(sbbh int constraint pk_sb_x2_sbbh primary key clustered(sbbh),col2 varchar(20))
create table sb_x3(sbbh int not null,col varchar(20),col1 varchar(30))
create table sb_x4(sbbh int not null,col varchar(20),col1 varchar(30))
create table sb_x5(sbbh int not null,col varchar(20),col1 varchar(30))
go
/*---------查询更改前需要的主键和表的情况---------------*/
select cast(a.name as varchar(30)) as table_name,cast(b.name as varchar(30)) as primary_name from(
select id,name from sysobjects where xtype='u' and name like 'sb_%') a
left join
(
select a.id,a.name from syscolumns a where exists (
select * from sysindexkeys b where a.id=b.id and a.colid=b.colid)
) b on a.id=b.id/*--------------建立游标进行操作----------------*/
declare t_cursor cursor for
select table_name from (select cast(a.name as varchar(30)) as table_name,cast(b.name as varchar(30)) as primary_name
from(select id,name from sysobjects where xtype='u' and name like 'sb_%') a
left join (
select a.id,a.name from syscolumns a where exists (
select * from sysindexkeys b where a.id=b.id and a.colid=b.colid)
) b on a.id=b.id) a where primary_name is null open t_cursor
declare @v1 varchar(200)
fetch next from t_cursor into @v1
while @@fetch_status=0
begin
exec('alter table '+@v1+' add constraint pk_'+@v1+'_sbbh primary key clustered(sbbh)')
fetch next from t_cursor into @v1
end
close t_cursor
deallocate t_cursor
/*-------------检查更改后的结果--------------*/
select cast(a.name as varchar(30)) as table_name,cast(b.name as varchar(30)) as primary_name from(
select id,name from sysobjects where xtype='u' and name like 'sb_%') a
left join
(
select a.id,a.name from syscolumns a where exists (
select * from sysindexkeys b where a.id=b.id and a.colid=b.colid)
) b on a.id=b.iddrop table sb_x1,sb_x2,sb_x3,sb_x4,sb_x5create table sb_x1(sbbh int constraint pk_sb_x1_sbbh primary key clustered(sbbh),col1 varchar(20))
create table sb_x2(sbbh int constraint pk_sb_x2_sbbh primary key clustered(sbbh),col2 varchar(20))
create table sb_x3(sbbh int not null,col varchar(20),col1 varchar(30))
create table sb_x4(sbbh int not null,col varchar(20),col1 varchar(30))
create table sb_x5(sbbh int not null,col varchar(20),col1 varchar(30))
go
/*---------查询更改前需要的主键和表的情况---------------*/
select cast(a.name as varchar(30)) as table_name,cast(b.name as varchar(30)) as primary_name from(
select id,name from sysobjects where xtype='u' and name like 'sb_%') a
left join
(
select a.id,a.name from syscolumns a where exists (
select * from sysindexkeys b where a.id=b.id and a.colid=b.colid)
) b on a.id=b.id/*--------------建立游标进行操作----------------*/
declare t_cursor cursor for
select table_name from (select cast(a.name as varchar(30)) as table_name,cast(b.name as varchar(30)) as primary_name
from(select id,name from sysobjects where xtype='u' and name like 'sb_%') a
left join (
select a.id,a.name from syscolumns a where exists (
select * from sysindexkeys b where a.id=b.id and a.colid=b.colid)
) b on a.id=b.id) a where primary_name is null open t_cursor
declare @v1 varchar(200)
fetch next from t_cursor into @v1
while @@fetch_status=0
begin
exec('alter table '+@v1+' add constraint pk_'+@v1+'_sbbh primary key clustered(sbbh)')
fetch next from t_cursor into @v1
end
close t_cursor
deallocate t_cursor
/*-------------检查更改后的结果--------------*/
select cast(a.name as varchar(30)) as table_name,cast(b.name as varchar(30)) as primary_name from(
select id,name from sysobjects where xtype='u' and name like 'sb_%') a
left join
(
select a.id,a.name from syscolumns a where exists (
select * from sysindexkeys b where a.id=b.id and a.colid=b.colid)
) b on a.id=b.iddrop table sb_x1,sb_x2,sb_x3,sb_x4,sb_x5table_name primary_name
------------------------------ ------------------------------
sb_x1 sbbh
sb_x2 sbbh
sb_x3 NULL
sb_x4 NULL
sb_x5 NULL(所影响的行数为 5 行)table_name primary_name
------------------------------ ------------------------------
sb_x1 sbbh
sb_x2 sbbh
sb_x3 sbbh
sb_x4 sbbh
sb_x5 sbbh(所影响的行数为 5 行)
'Alter Table '+name+' Add Constraint constr_PK Primary Key (sbbh)' PK
from sysobjects s
where xtype='u' and name like 'SB_%'
and not Exists
(Select 1 From sysindexes where id = s.id And Index_Col(s.Name,indid,1) = sbbh)此查询将返回两列,第一列的目的是将这些表的sbbh列设置为NOT NULL,第二列是执行添加主键的操作.先执行第一列的所有内容,再执行第二列的所有内容.因为如果有些表的sbbh可以NULL的话,则ALTER TABLE ADD CONSTRAINT操作会报错.