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

轉載于:https://www.cnblogs.com/tonykan/p/3439680.html

總結

以上是生活随笔為你收集整理的如何删除数据库中的所有用户表（表与表之间有外键关系）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。