如何删除数据库中的所有用户表（表与表之间有外键关系）
1、由表名求字段名
create proc up_008(@table varchar(20))
as
begin
declare @sql varchar(99)
select @sql=\'select name from syscolumns where id=object_id(\'
select @sql=@sql+\'\'\'\'+@table+\'\'\'\'+\')\'
--select @sql
exec(@SQL)
end
exec up_008 a_idx2
2、编程删除数据库中的用户表
create proc up_010
as
begin
declare @sql varchar(99),@tbl varchar(30),@fk varchar(30)
declare cur_fk cursor local for
select object_name(constid),object_name(fkeyid) from sysreferences
--删除所有外键
open cur_fk
fetch cur_fk into @fk,@tbl
while @@fetch_status =0
begin
select @sql=\'alter table \'+@tbl+\' drop constraint \'+@fk
exec(@SQL)
--select @sql=\'drop table \'+@tbl
fetch cur_fk into @fk,@tbl
end
close cur_fk
declare cur_fks cursor local for
select name from sysobjects where xtype=\'U\'
open cur_fks
fetch cur_fks into @tbl
while @@fetch_status =0
begin
select @sql=\'drop table [\'+@tbl+\']\'
exec(@SQL)
fetch cur_fks into @tbl
end
close cur_fks
end
exec up_010
--declare @sql varchar(99)
--select @sql=\'alter table Orders drop constraint FK_Orders_Customers\'
--exec(@SQL)
--select * from sysreferences
--select object_name(constid),object_name(fkeyid) from sysreferences
--alter table Orders drop constraint FK_Orders_Customers
3、统计数据库中每个用户表的记录数
create proc up_011
as
begin
create table #tmp (qty int)
create table #tmp1 (tbl varchar(30),qty int)
declare @sql varchar(99),@tbl varchar(30),@qty int
declare cur_fks cursor local for
select name from sysobjects where xtype=\'U\'
open cur_fks
fetch cur_fks into @tbl
while @@fetch_status =0
begin
select @sql=\'select count(*) from \'+@tbl
insert into #tmp exec(@SQL)
select @qty=qty from #tmp
insert into #tmp1 values(@tbl,@qty)
delete from #tmp
fetch cur_fks into @tbl
end
close cur_fks
select * from #tmp1
end
exec up_011
create table #tmp(qty int)
insert into #tmp exec(\'select count(*) from sales\')
select * from #tmp
4 求表中两行记录之差
-----求表中两条记录之差
create table a099
(
id int,
qty int
)
create procedure p015
as
begin
declare @max int,@sql varchar(999)
select @max=count(*) from a099
select @max=@max-1
select * into #tmp1 from a099 where 0=1
select @sql=\'select top \'+cast(@max as varchar(9))+\' * from a099\'
insert into #tmp1 exec (@sql)
declare cur_1 cursor local for select * from #tmp1
select * into #tmp2 from a099 where 0=1
select @sql=\'select top \'+cast(@max as varchar(9))+\' * from a099 order by 1 desc\'
insert into #tmp2 exec (@sql)
declare cur_2 cursor local for select * from #tmp2 order by 1
--计算两个游标之差
declare @id int,@id1 int,@qty int,@qty1 int,@diff int
declare @tbl table(id int identity(1,1),diff int)
open cur_1
open cur_2
fetch cur_1 into @id,@qty
fetch cur_2 into @id1,@qty1
while @@fetch_status=0
begin
select @diff=@qty1-@qty
insert into @tbl(diff) values(@diff)
fetch cur_1 into @id,@qty
fetch cur_2 into @id1,@qty1
end
close cur_1
close cur_2
--计算两个游标之差
drop table #tmp1
drop table #tmp2
select * from @tbl
end
exec p015
select * from a099
select * from a099 a,a099 b where a.id=b.id
--简单的求差问题
alter proc p016
as
begin
declare cur_1 cursor local for select * from a099 order by 1
declare @id int,@qty int,
@id1 int,@qty1 int,@diff int
declare @tbl table(id int identity(1,1),qty int)
open cur_1
fetch cur_1 into @id ,@qty
while @@fetch_status=0
begin
if @qty1<>0
begin
select @diff=@qty1-@qty
select @qty=@qty1
insert into @tbl(qty) values(@diff)
end
fetch cur_1 into @id1,@qty1
end
close cur_1
select * from @tbl
end
exec p016
select * from a099 a,a099 b where a.id=b.id
--最简单的求差计算
create procedure p017
as
begin
select identity(int,1,1) id,qty into #tmp from a099
declare cur_1 cursor local for
select a.id,a.qty q1,b.qty q2 from #tmp a,#tmp b where a.id=b.id-1
declare @id int,@q1 int,@q2 int,@diff int
declare @tbl table(id int identity(1,1),qty int)
open cur_1
fetch cur_1 into @id,@q1,@q2
while @@fetch_status=0
begin
select @diff=@q2-@q1
insert into @tbl(qty) values(@diff)
fetch cur_1 into @id,@q1,@q2
end
close cur_1
select * from @tbl
end
exec p015
go
exec p016
go
exec p017
go
