請複製以下的程式碼
create table [dbo].[tablename](
[class] [nvarchar] (200) null
,[TableName] [nvarchar] (400) not null
,[Trad] [nvarchar] (400) null
,[FieldCount] [int] null
,[DataCount] [int] null
,[note] [nvarchar] (800) null
,[InsertDate] [datetime] null
,[UpdateDate] [datetime] null)
GO
create table [dbo].[tablename_bak](
[class] [nvarchar] (200) null
,[TableName] [nvarchar] (400) not null
,[Trad] [nvarchar] (400) null
,[FieldCount] [int] null
,[DataCount] [int] null
,[note] [nvarchar] (800) null
,[InsertDate] [datetime] null
,[UpdateDate] [datetime] null)
GO
create table [dbo].[tabledesc](
[id] [int] not null
,[TableName] [nvarchar] (800) not null
,[Trad] [nvarchar] (200) null
,[Eng] [nvarchar] (2000) not null
,[DataType] [nchar] (80) null
,[Length] [int] null
,[xprec] [int] null
,[xscale] [int] null
,[IsNullable] [int] null
,[GVReadOnly] [nvarchar] (40) null
,[GVisible] [nvarchar] (40) null
,[GVtxtLen] [int] null
,[DVsys] [char] (3) null
,[Control] [nchar] (40) null
,[identiy] [int] null
,[tips] [nvarchar] (800) null)
GO
create table [dbo].[tabledesc_bak](
[id] [int] not null
,[TableName] [nvarchar] (800) not null
,[Trad] [nvarchar] (200) null
,[Eng] [nvarchar] (2000) not null
,[DataType] [nchar] (80) null
,[Length] [int] null
,[xprec] [int] null
,[xscale] [int] null
,[IsNullable] [int] null
,[GVReadOnly] [nvarchar] (40) null
,[GVisible] [nvarchar] (40) null
,[GVtxtLen] [int] null
,[DVsys] [char] (3) null
,[Control] [nchar] (40) null
,[identiy] [int] null
,[tips] [nvarchar] (800) null)
GO
sp_dbcmptlevel wg,80 GO
create proc myfn(
@order as char(1) = null
)
as
if @order is null
begin
select 'sp_helptext '+name as '自訂function:'
,'dbo.'+name+'()'
,crdate
from [sysobjects] where xtype in ('fn')
and substring (name,1,2) not in ('SP','DT')
and substring (name,1,6) not in ('ASPNET')
order by 1
end
else
select 'sp_helptext '+name as '自訂function:'
,'dbo.'+name+'()'
,crdate
from [sysobjects] where xtype in ('fn')
and substring (name,1,2) not in ('SP','DT')
and substring (name,1,6) not in ('ASPNET')
order by case when @order=1 then name else crdate end desc
--end
GO
create function fn_myInsert(
@tablename as nvarchar(50),
@variable as char(1)
)
returns varchar(3000)
as
begin
--cursor 逐一記錄每張卡號
declare cardlist cursor for
select b.name from [sysobjects] a, [syscolumns] b
where a.id=b.id and a.name=@tablename
open cardlist; --開啟Cursor
declare @i as int;
declare @cardno as varchar(60);
declare @cardnos as varchar(3000); set @cardnos='';--累積值
fetch next from cardlist into @cardno;
while (@@fetch_status=0) --0表示有資料。
begin
if(@variable<>'@') set @cardnos=@cardnos+@cardno+','
if(@variable='@')
if(lower(@cardno) not in ('insertdate','updatetime'))
begin set @cardnos=@cardnos+'@'+@cardno+',' end
else
begin set @cardnos=@cardnos+'getdate(),' end
fetch next from cardlist into @cardno;
end
set @cardnos=substring(@cardnos,1,len(@cardnos)-1)
close cardlist; --關閉Cursor與資料表的連結
deallocate cardlist; --移除Cursor
return @cardnos;
end
--end
GO
create proc myInsert(
@tablename as varchar(100)
)
as
select ' insert into ['+@tablename+']' as '請複製該段SQL碼，加快開發速度，減少打錯字因素'
union select '('+dbo.fn_myInsert(@tablename,'')+') '
union select 'values ('+dbo.fn_myInsert(@tablename,'@')+')'
--end
GO
create proc mysp(
@order as char(1) = null
)
as
if @order is null
begin
select 'sp_helptext '+name as '自訂預存程序名稱:---------', crdate
from sysobjects
where xtype in ('P')
and substring (name,1,2) not in ('SP','DT')
--and substring (name,1,6) not in ('ASPNET')
order by 1
end
else
select 'sp_helptext '+name as '自訂預存程序名稱:---------', crdate
from sysobjects
where xtype in ('P')
and substring (name,1,2) not in ('SP','DT')
--and substring (name,1,6) not in ('ASPNET')
order by case when @order=1 then name else crdate end desc
--end
GO
create proc mytable(
@TableName22 as varchar(100) =null
)
as
declare @maxid as int;
select @maxid=max(colorder) from syscolumns a ,sysobjects c
where a.id=c.id and c.name=@TableName22
if @TableName22 is not null
begin
exec sp_spaceused @TableName22;
SET QUOTED_IDENTIFIER ON;
select
sn=0,欄位='',型態='',長度=0,精確值='',建立日期='',
'更新用'='update ['+@TableName22 +'] set ',
'更新用2'='update ['+@TableName22 +'] set ',
'逗號'='',
',@變數'='',
',[欄位]=@變數',
'Query'='select',
'SP'='create proc [](',
'CreateTable'='create table [dbo].['+@TableName22+'](',
'MYSQL'='create table '+@TableName22+' ('
union all
select
/*序號*/a.colorder,
/*欄位*/a.name,
/*型態*/b.name,
/*長度*/a.length,
/*精確*/case when b.name in ('int','decimal') then '('+convert(varchar(10), a.xprec)+','+convert(varchar(10),a.xscale)+')' else '' end,
/*建立日期*/convert(varchar(20),c.crdate,111),
/*更新用*/
'+'',['+a.name+']=''''''+'+
case
when b.name in ('float','money','smallint','decimal') then 'convert(varchar(20),IsNull(Rtrim('+a.name+'),0))+'
when b.name in ('datetime') then 'convert(varchar(20),IsNull('+a.name+',''''),120)+'
else 'IsNull(Rtrim('+a.name+'),'''')+'
end+'''''',
/*更新用2*/',['+a.name+']=@'+a.name,
/*逗號*/','+a.name,
/*@變數*/',@'+a.name,
/*[欄位]+@變數*/
'+'', ['+a.name+']=''+'+
case
when b.name in ('float','money','smallint','decimal') then 'convert(varchar(10),IsNull(@'+a.name+',0),111)'
when b.name in ('datetime') then 'convert(varchar(10),IsNull(@'+a.name+',''),111)'
else 'IsNull(@'+a.name+','''')'
end+'+'+'''''''',
/*select*/
case when a.colorder=1 then '' else ',' end +
case
when b.name in ('datetime','float','money') then 'convert(varchar(10),'+a.name+',111) as '+a.name
when b.name not in ('datetime','float','money') then a.name+' as '+a.name
end,
/*StoreProcedure*/
case when a.colorder=1 then '' else ',' end
+case
when b.name='decimal' then '@'+a.name+' as decimal '+'('+convert(varchar(10),a.xprec)+','+convert(varchar(10),a.xscale)+')'
when b.name in ('nvarchar','varchar','nchar','char') then '@'+a.name+' as '+b.name+' ('+convert(varchar(10),a.length)+') '
when b.name in ('float','smallint','int','money','datetime') then '@'+a.name+' as '+b.name+''
end
+case when @maxid=a.colorder then ')' else '' end,
/*CreateTable*/
case when a.colorder=1 then '' else ',' end
+'['+a.name+'] ['+b.name+'] '
+case
when b.name='decimal' then '('+convert(varchar(10),a.xprec)+','+convert(varchar(10),a.xscale)+')'
when b.name in ('nvarchar','varchar','nchar','char') then '('+convert(varchar(10),a.length)+') '
when b.name in ('float','smallint','int','money','datetime') then ''
end+
+case when a.isnullable=0 then ' not null' else 'null' end
+case when @maxid=a.colorder then ')' else '' end,
/*MySQL*/
case when a.colorder=1 then '' else ',' end
+''+a.name+' '
+case
when b.name='decimal' then b.name+' ('+convert(varchar(10), a.xprec)+','+convert(varchar(10),a.xscale)+')'
when b.name in ('varchar','nvarchar') then 'varchar ('+convert(varchar(10), a.length)+') '
when b.name in ('char','nchar') then 'char ('+convert(varchar(10), a.length)+') '
when b.name in ('float','smallint','int','money','datetime') then b.name
end
+case when a.isnullable=0 then ' not null' else ' null' end
+case when @maxid=a.colorder then ')' else '' end
from [syscolumns] a ,[systypes] b, [sysobjects] c
where
a.id=c.id and a.xtype=b.xtype
and c.type='U' and b.name<>'sysname'
and c.name=@TableName22
order by 1;
end
else
select b.class as '資料表分類',a.name as '英文',b.trad as '中文',b.fieldCount as '欄位數目',crdate
from [sysobjects] a,[tablename] b
where a.type='U' and a.name=b.tablename order by 5 desc;
--end
GO
create proc mytable_update
as
delete from tabledesc_bak; --清空備份資料
insert into tabledesc_bak select * from tabledesc --先備份資料
delete from TableDesc; --清空實體資料庫
insert into TableDesc --從實體資料庫組合部分Tabledesc
select
id=b.colorder,
'資料表'=a.name,
'中文'='',
'英文'=b.name,
'型別'=c.name,
'長度'=b.length,
'整數'=b.xprec,
'小數'=isNull(b.scale,0),
'isNullable'=b.isnullable,
'GVReadOnly'=null,
'GVisible'=null,
'GVtxtLen'=null,
'DVSys'=null,
'Control'='txt',--預設
'identity'=b.colstat,
'tips'=null
from sysobjects a,syscolumns b,systypes c
where
a.id=b.id and b.xtype=c.xtype
and a.xtype='U'
and c.name<>'sysname'
order by 1
;
/* 從備份資料修改已存在欄位*/
update tabledesc
set
trad=b.trad,
GVReadOnly=isnull(b.GVReadOnly,'False'),
GVisible=b.GVisible,
GVtxtLen=b.GVtxtLen,
DVsys=b.DVsys,
control=isnull(b.control,'txt'),
tips=b.tips
from [tabledesc] a,[tabledesc_bak] b
where a.tablename=b.tablename
and a.eng=b.eng
;
/*沒有中文的部份預設英文*/
update tabledesc
set trad=eng
where trad='';
/*統計變更前後欄位差異數量*/
select
b.tablename ,
'變更前'=isNull(a.cc,0),
'變更後'=b.dd,
'欄差異量'=b.dd-isNull(a.cc,0)
from
(select tablename,count(*) as cc from tabledesc_bak group by tablename) as a,
(select tablename,count(*) as dd from tabledesc group by tablename) as b
where a.tablename=*b.tablename
order by 4 desc,1
;
/*TableName維護說明*/
delete from TableName_bak;--清除備份
insert into TableName_bak --備份資料
select *
from TableName --清除實體資料
;
delete from [TableName]
;
insert into [TableName]
select
null,TableName,null,count(*),null,null,null,null
from TableDesc
group by TableName
;
update a
set
class=b.class,
Trad=b.Trad, --補充中文說明
DataCount=c.rows, --補充資料筆數
InsertDate=c.crdate, --建立日期
UpdateDate=getdate() --變更資料日期
from
TableName a,
TableName_bak b,
(select
a.name as tablename,
max(b.rows) as rows,
max(a.crdate) as crdate
from sysobjects a,sysindexes b
where
a.id=b.id
and a.xtype='U'
group by a.name) as c
where
a.TableName=b.TableName
and a.TableName=c.TableName
;
--select '新報表',* from TableName where class is null
--select '舊報表',* from TableName where class is not null
--end
GO
create proc mytabledesc (
@tn as nvarchar(100)
)as
select
id,trad,eng,
rtrim(datatype)+'('+convert(varchar(10),length)+')' as datatype,isnullable
,gvreadonly ,gvisible,gvtxtlen,dvsys,control,identiy,tips
from tabledesc where tablename=@tn
--end
GO
--end
複製完了之後，先執行 exec update_tabledesc 指令，將所有必要的資料更新入暫存檔內，接下來就可在MSSQL內的查詢視窗內調出你需要的所有指令了。
exec mytable 'test'
exec myinsert 'test'
exec mytabledesc 'test'
exec mysp
exec myfn
範例一：假設你想查閱你的資料庫內有多少個資料表，各有多少欄位，你可以指令下列mytalbe指令，其中我擴充了資料表分類、中英文對照的欄位，預設就是NULL沒有。
exec mytable
--查詢結果-----------------------
資料表分類 英文 中文 欄位數目 產生日期
NULL tablename NULL 8 2008-10-01 13:37:20.420
NULL tabledesc NULL 16 2008-10-01 13:37:20.420
NULL issue NULL 7 2008-10-01 12:34:45.483
NULL issues NULL 5 2008-09-26 17:09:06.890
NULL billboard NULL 4 2008-09-18 10:05:22.950
沒有留言:
張貼留言