1，xtype use
NorthWind go select
* from sysobjects where xtype='U' xtype參數的意思 C
= CHECK 約束
D = 默認值或 DEFAULT 約束
F = FOREIGN KEY 約束
L = 日志
FN =
標量函數
IF = 內嵌表函數
P = 存儲過程
PK = PRIMARY KEY 約束（類型是 K）
RF =
復制篩選存儲過程
S = 系統表
TF = 表函數
TR = 觸發器
U = 用戶表
UQ = UNIQUE 約束（類型是
K）
V = 視圖
X = 擴展存儲過程 --刪除表 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO IF OBJECT_ID(StoredProceduresBackup) IS NOT NULL
DROP TABLE StoredProceduresBackup
GO IF OBJECT_ID(N'STUDENT') IS NOT NULL
DROP TABLE STUDENT GO

select TABLE_NAME,column_name from INFORMATION_SCHEMA.COLUMNS ,sysobjects where COLUMN_NAME='DACode' group by TABLE_NAME,COLUMN_NAME

use IC_Test
go

--創建存放 所有表 所有列的 全局臨時表
drop table ##t1
select distinct table_name,column_name,data_type into ##T1 from INFORMATION_SCHEMA.COLUMNS,sysobjects
go

--創建存放有數據的臨時表和臨時列
drop table ##t2
create table ##t2(tablename varchar(20),tablecolumn varchar(20),rowsum int default(0),id int identity(1,1) primary key)
go
insert into ##t2 values ('dfswer','df' ,0)
select * from ##t2
go

declare cursor_a scroll cursor for
select * from ##T1
go
open cursor_a
go



while @@FETCH_STATUS=0
begin

declare @@table_name varchar(20)
declare @@column_name varchar(20)
declare @@data_type varchar(20)
fetch next from cursor_a into @@table_name,@@column_name,@@data_type
--select @@table_name,@@column_name
--select * from ##t2

declare @sql varchar(2000)
set @sql='
declare @@table_name varchar(20)
declare @@column_name varchar(20)
declare @@rowsum1 int
set @@table_name='''+@@table_name+'''
set @@column_name='''+@@column_name+'''



set @@rowsum1=(select count('+@@column_name+') from '+@@table_name+')
select @@rowsum1
if @@rowsum1>0
begin
insert into ##T2 values('''+@@table_name+''','''+@@column_name+''',@@rowsum1)
end


'
--select @sql
exec(@sql)
end

close cursor_a
deallocate cursor_a


select * from ##t2
select distinct tablename from ##t2

轉載于:https://www.cnblogs.com/netact/archive/2011/05/13/2045936.html

總結

以上是生活随笔為你收集整理的SQL SERVER 高级数据库脚本编程的全部內容，希望文章能夠幫你解決所遇到的問題。

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