SQL SERVER 高级数据库脚本编程

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