SQL SERVER 高级数据库脚本编程

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