–此判断不严密

SQL Server编写存储过程小工具 以下是两个存储过程的源程序,sp,if @objectId is null — 判斷對象是否存在,1 –,Master..SysDatabases order by Name,*,return

主导提示:SQL Server编写存款和储蓄进度小工具以下是五个存款和储蓄进程的源程序

USE [XXX] GO

总结网络资料收拾]

用这一个蕴藏进程能够达成:

SQL Server编写存储进度小工具 以下是五个存款和储蓄进度的源程序
/*===========================================================

QUOTED_IDENTIFIER ON GO

1.收获具有数据库名:
   (1)、Select Name FROM
Master..SysDatabases order by Name
2.**收获具备表名:

CREATE PROCEDURE
dbo.UspOutputData
@tablename sysname
AS
declare @column
varchar(1000)
declare @columndata
varchar(1000)
declare @sql
varchar(4000)
declare @xtype
tinyint
declare @name
sysname
declare @objectId
int
declare @objectname
sysname
declare @ident
int

语法: sp_GenInsert Table Name,Stored Procedure Name 以northwind
数据库为例 sp_GenInsert Employees, INS_Employees

ALTER PROCEDURE [dbo].[UspOutputData]

**   (1)、Select Name FROM SysObjects Where
XType=’U’ orDER BY Name
           XType=’U’:表示全体顾客表;
           XType=’S’:表示具备系统表;

set nocount on
set @objectId=object_id(@tablename)

批注:假如您在Master系统数据库中创建该进度,那你就可以在你服务器上具有的数据库中央银行使该进度。

@tablename sysname

   (2)、SELECT name FROM sysobjects WHERE type = ‘U’ AND sysstat =
’83’

if @objectId
is null
— 判斷對象是或不是存在
begin
print ‘The object
not exists’
return
end
set @objectname=rtrim(object_name(@objectId))

=============================================================*/

AS

           注意:平时景色只必要type =
‘U’,但一时会有连串表混在里面(不亮堂哪些原因),加上前边一句后就能够去除那个体系表了

if @objectname
is null
or charindex(@objectname,@tablename卡塔尔=0 –此决断不严峻
begin
print ‘object not
in current database’
return
end

CREATE procedure sp_GenInsert @TableName varchar(130), @ProcedureName
varchar(130) as set nocount on

declare @column varchar(2000)

3.**获取具备字段名:**
(1)、Select Name FROM SysColumns Where
id=Object_Id(‘TableName’)

if OBJECTPROPERTY(@objectId,’IsTable’)< >
1 —
判斷對象是不是是table
begin
print ‘The object
is not table’
return
end

declare @maxcol int, @TableID int

declare @columndata varchar(2000)

(2)、SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length
FROM syscolumns, systypes WHERE syscolumns.xusertype =
systypes.xusertype AND “syscolumns.id =
object_id(‘tableName’)

select @ident=status&0x80 from
syscolumns where id=@objectid
and status&0x80=0x80

set @TableID = object_id(@TableName)

declare @sql varchar(4000)

       注意点:
     (a)这里为了首要优质有些关键内容,接纳了在这之中几项消息输出。
     (b)syscolumns表中只含有数据类型编号,要拿走完整的名字需求从systypes表中找,平常客商选择的数据类型用xusertype对应比较好,不会现身部分多的情状。
     (c)syscolumns.length取得的是物理内部存款和储蓄器的尺寸,所以nvarchar和varchar等类别在数据库中的展现是以此的十分之五。

if @ident
is not
null
print ‘SET
IDENTITY_INSERT ‘+@TableName+’
ON’

select @MaxCol = max(colorder) from syscolumns where id = @TableID

declare @xtype tinyint

 

declare syscolumns_cursor cursor
for select
c.name,c.xtype from syscolumns c
where c.id=@objectid
order by
c.colid
open syscolumns_cursor
set @column=”
set @columndata=”
fetch next
from syscolumns_cursor into
@name,@xtype
while @@fetch_status <
>-1
begin
if @@fetch_status <
>-2
begin
if @xtype
not in(189,34,35,99,98卡塔尔 –timestamp不需处理,image,text,ntext,sql_variant
权且不处理
begin
set @column=@column+case
when len(@column)=0 then”
else ‘,’end+@name
set @columndata=@columndata+case
when len(@columndata)=0 then
” else
‘,”,”,’end
+case
when @xtype
in(167,175) then
””””’+’+@name+’+””””’
–varchar,char
when @xtype
in(231,239) then
”’N”””+’+@name+’+””””’
–nvarchar,nchar
when @xtype=61 then
””””’+convert(char(23),’+@name+’,121)+””””’
–datetime
when @xtype=58 then
””””’+convert(char(16),’+@name+’,120)+””””’
–smalldatetime
when @xtype=36 then
””””’+convert(char(36),’+@name+’)+””””’
–uniqueidentifier
else @name
end
end
end
fetch next
from syscolumns_cursor into
@name,@xtype
end
close syscolumns_cursor
deallocate syscolumns_cursor

select Create Procedure + rtrim(@ProcedureName) as type,0 as colorder
into #TempProc union select convert(char(35),@ + syscolumns.name) +
rtrim(systypes.name) + case when rtrim(systypes.name) in
(binary,char,nchar,nvarchar,varbinary,varchar) then ( +
rtrim(convert(char(4),syscolumns.length)) + ) when rtrim(systypes.name)
not in (binary,char,nchar,nvarchar,varbinary,varchar) then end + case
when colorder @maxcol then , when colorder = @maxcol then end as type,
colorder from syscolumns join systypes on syscolumns.xtype =
systypes.xtype where id = @TableID and systypes.name sysname union
select AS,@maxcol + 1 as colorder union select INSERT INTO +
@TableName,@maxcol + 2 as colorder union select (,@maxcol + 3 as
colorder union select syscolumns.name + case when colorder @maxcol then
, when colorder = @maxcol then end as type, colorder + @maxcol + 3 as
colorder from syscolumns join systypes on syscolumns.xtype =
systypes.xtype where id = @TableID and systypes.name sysname union
select ),(2 * @maxcol) + 4 as colorder union select VALUES,(2 *
@maxcol) + 5 as colorder union select (,(2 * @maxcol) + 6 as colorder
union select @ + syscolumns.name + case when colorder @maxcol then ,
when colorder = @maxcol then end as type, colorder + (2 * @maxcol + 6)
as colorder from syscolumns join systypes on syscolumns.xtype =
systypes.xtype where id = @TableID and systypes.name sysname union
select ),(3 * @maxcol) + 7 as colorder order by colorder

declare @name sysname

4、获得表中主键所富含的列名:

set @sql=’set
nocount on select ”insert ‘+@tablename+'(‘+@column+’)
values(”as ”–”,’+@columndata+’,”)” from
‘+@tablename

select type from #tempproc order by colorder

declare @objectId int

    SELECT syscolumns.name FROM
syscolumns,sysobjects,sysindexes,sysindexkeys WHERE syscolumns.id =
object_id(‘tablename’) AND sysobjects.xtype = ‘PK’ AND
sysobjects.parent_obj = syscolumns.id AND sysindexes.id = syscolumns.id
AND sysobjects.name = sysindexes.name AND sysindexkeys.id =
syscolumns.id AND sysindexkeys.indid = sysindexes.indid AND
syscolumns.colid = sysindexkeys.colid

print ‘–‘+@sql
exec(@sql)

drop table #tempproc

declare @objectname sysname

专心:那是在4张系统表中找出的,关系相比复杂,大概能够象征为:
syscolumns中存有表中的列音信和表id,sysobjects表中存有主键名字(即PK_Table相符)和表id,sysindexes中存
有主键名字和表id和index编号,sysindexkeys中存有表id和index编号和列编号,一项一项对应起来后就会找到列名了。

if @ident
is not
null
print ‘SET
IDENTITY_INSERT ‘+@TableName+’
OFF’

declare @ident int

 

GO

set nocount on set @objectId=object_id(@tablename)

另外的SQL代码

行使格局为:

if @objectId is null — 判斷對象是还是不是留存

select syscolumns.name,
systypes.name,
syscolumns.length from syscolumns   

在询问分析器以“文本展现结果”方法推行
exec UspOutputData 你的表名
然后将运营后的结果存成.sql,加上用SQL Server生成的数据库脚本就能够了。

begin

  left join
systypes on syscolumns. xusertype
=systypes. xusertype
 

弱点和主题材料:

print ‘The object not exists’

  where id=(select
id from sysobjects where name=’订货主档’卡塔尔(قطر‎;

收获导出数据的说话,但image,text,ntext,sql_variant
列不出现在说话,今后修正。

return

go;

end

抑或用如此的写法,实践结果一致:

set @objectname=rtrim(object_name(@objectId))

select syscolumns.name,
systypes.name,
syscolumns.length from syscolumns,systypes

if @objectname is null or charindex(@objectname,@tablename卡塔尔(قطر‎=0
–此剖断不紧凑

where (syscolumns.id=object_id(‘订货主档’State of Qatar and syscolumns.xusertype=systypes.xusertype)

begin

order
by syscolumns.colorder;

print ‘object not in current database’

go