仓酷云

标题: MSSQL网站制作之保藏几段SQL Server语句和存储历程 [打印本页]

作者: 逍遥一派 时间: 2015-1-16 22:40
标题: MSSQL网站制作之保藏几段SQL Server语句和存储历程
对于insert和delete，event中包含了插入/删除的记录的所有字段的值(太爽了。。)server|存储历程|语句
--======================================================

--列出SQLSERVER一切表，字段名，主键，范例，长度，小数位数等信息

--在查询剖析器里运转便可,能够天生一个表，导出到EXCEL中

--======================================================

SELECT

(casewhena.colorder=1thend.nameelseend)表名,

a.colorder字段序号,

a.name字段名,

(casewhenCOLUMNPROPERTY(a.id,a.name,IsIdentity)=1then√elseend)标识,

(casewhen(SELECTcount(*)

FROMsysobjects

WHERE(namein

(SELECTname

FROMsysindexes

WHERE(id=a.id)AND(indidin

(SELECTindid

FROMsysindexkeys

WHERE(id=a.id)AND(colidin

(SELECTcolid

FROMsyscolumns

WHERE(id=a.id)AND(name=a.name)))))))AND

(xtype=PK))>0then√elseend)主键,

b.name范例,

a.length占用字节数,

COLUMNPROPERTY(a.id,a.name,PRECISION)as长度,

isnull(COLUMNPROPERTY(a.id,a.name,Scale),0)as小数位数,

(casewhena.isnullable=1then√elseend)同意空,

isnull(e.text,)默许值,

isnull(g.[value],)AS字段申明

FROMsyscolumnsaleftjoinsystypesb

ona.xtype=b.xusertype

innerjoinsysobjectsd

ona.id=d.idandd.xtype=Uandd.namedtproperties

leftjoinsyscommentse

ona.cdefault=e.id

leftjoinsyspropertiesg

ona.id=g.idANDa.colid=g.smallid

orderbya.id,a.colorder

-------------------------------------------------------------------------------------------------

列出SQLSERVER一切表、字段界说，范例，长度，一个值等信息

并导出到Excel中

--======================================================

--Exportallusertablesdefinitionandonesamplevalue

--jan-13-2003,Dr.Zhang

--======================================================

在查询剖析器里运转：

SETANSI_NULLSOFF

GO

SETNOCOUNTON

GO

SETLANGUAGESimplifiedChinese

go

DECLARE@tblnvarchar(200),@fldnvarchar(200),@sqlnvarchar(4000),@maxlenint,@samplenvarchar(40)

SELECTd.nameTableName,a.nameFieldName,b.nameTypeName,a.lengthLength,a.isnullableIS_NULLINTO#t

FROMsyscolumnsa,systypesb,sysobjectsd

WHEREa.xtype=b.xusertypeanda.id=d.idandd.xtype=U

DECLAREread_cursorCURSOR

FORSELECTTableName,FieldNameFROM#t

SELECTTOP1_TableNameTableName,

FieldNameFieldName,TypeNameTypeName,

LengthLength,IS_NULLIS_NULL,

MaxLenUsedASMaxLenUsed,SampleValueSample,

CommentCommentINTO#tcFROM#t

OPENread_cursor

FETCHNEXTFROMread_cursorINTO@tbl,@fld

WHILE(@@fetch_status-1)---failes

BEGIN

IF(@@fetch_status-2)--Missing

BEGIN

SET@sql=NSET@maxlen=(SELECTmax(len(cast(+@fld+asnvarchar)))FROM+@tbl+)

--PRINT@sql

EXECSP_EXECUTESQL@sql,N@maxlenintOUTPUT,@maxlenOUTPUT

--print@maxlen

SET@sql=NSET@sample=(SELECTTOP1cast(+@fld+asnvarchar)FROM+@tbl+WHERElen(cast(+@fld+asnvarchar))=+convert(nvarchar(5),@maxlen)+)

EXECSP_EXECUTESQL@sql,N@samplevarchar(30)OUTPUT,@sampleOUTPUT

--forquickly

--SET@sql=NSET@sample=convert(varchar(20),(SELECTTOP1+@fld+FROM+

--@tbl+orderby1desc))

PRINT@sql

print@sample

print@tbl

EXECSP_EXECUTESQL@sql,N@samplenvarchar(30)OUTPUT,@sampleOUTPUT

INSERTINTO#tcSELECT*,ltrim(ISNULL(@maxlen,0))asMaxLenUsed,

convert(nchar(20),ltrim(ISNULL(@sample,)))asSample,CommentFROM#twhereTableName=@tblandFieldName=@fld

END

FETCHNEXTFROMread_cursorINTO@tbl,@fld

END

CLOSEread_cursor

DEALLOCATEread_cursor

GO

SETANSI_NULLSON

GO

SETNOCOUNTOFF

GO

selectcount(*)from#t

DROPTABLE#t

GO

selectcount(*)-1from#tc

select*into##txfrom#tcorderbytablename

DROPTABLE#tc

--select*from##tx

declare@dbnvarchar(60),@sqlnvarchar(3000)

set@db=db_name()

--请修正用户名和口令导出到Excel中

set@sql=execmaster.dbo.xp_cmdshellbcp..dbo.##txoutc:+@db+_exp.xls-w-C936-
Usa
-Psa

print@sql

exec(@sql)

GO

DROPTABLE##tx

GO

--======================================================

--依据表中数据天生insert语句的存储历程

--创建存储历程，实行spGenInsertSQL表名

--感激playyuer

--======================================================

CREATEprocspGenInsertSQL(@tablenamevarchar(256))

as

begin

declare@sqlvarchar(8000)

declare@sqlValuesvarchar(8000)

set@sql=(

set@sqlValues=values(+

select@sqlValues=@sqlValues+cols++,+,@sql=@sql+[+name+],

from

(selectcase

whenxtypein(48,52,56,59,60,62,104,106,108,122,127)

thencasewhen+name+isnullthenNULLelse+cast(+name+asvarchar)+end

whenxtypein(58,61)

thencasewhen+name+isnullthenNULLelse+++cast(+name+asvarchar)+++end

whenxtypein(167)

thencasewhen+name+isnullthenNULLelse+++replace(+name+,,)+++end

whenxtypein(231)

thencasewhen+name+isnullthenNULLelse+N++replace(+name+,,)+++end

whenxtypein(175)

thencasewhen+name+isnullthenNULLelse+++cast(replace(+name+,,)asChar(+cast(lengthasvarchar)+))++end

whenxtypein(239)

thencasewhen+name+isnullthenNULLelse+N++cast(replace(+name+,,)asChar(+cast(lengthasvarchar)+))++end

elseNULL

endasCols,name

fromsyscolumns

whereid=object_id(@tablename)

)T

set@sql=selectINSERTINTO[+@tablename+]+left(@sql,len(@sql)-1)+)+left(@sqlValues,len(@sqlValues)-4)+)from+@tablename

--print@sql

exec(@sql)

end

GO

--======================================================

--依据表中数据天生insert语句的存储历程

--创建存储历程，实行proc_insert表名

--感激Sky_blue

--======================================================

CREATEprocproc_insert(@tablenamevarchar(256))

as

begin

setnocounton

declare@sqlstrvarchar(4000)

declare@sqlstr1varchar(4000)

declare@sqlstr2varchar(4000)

select@sqlstr=selectinsert+@tablename

select@sqlstr1=

select@sqlstr2=(

select@sqlstr1=values(+

select@sqlstr1=@sqlstr1+col++,+,@sqlstr2=@sqlstr2+name+,from(selectcase

--whena.xtype=173thencasewhen+a.name+isnullthenNULLelse+convert(varchar(+convert(varchar(4),a.length*2+2)+),+a.name+)+end

whena.xtype=104thencasewhen+a.name+isnullthenNULLelse+convert(varchar(1),+a.name+)+end

whena.xtype=175thencasewhen+a.name+isnullthenNULLelse+++replace(+a.name+,,)+++end

whena.xtype=61thencasewhen+a.name+isnullthenNULLelse+++convert(varchar(23),+a.name+,121)+++end

whena.xtype=106thencasewhen+a.name+isnullthenNULLelse+convert(varchar(+convert(varchar(4),a.xprec+2)+),+a.name+)+end

whena.xtype=62thencasewhen+a.name+isnullthenNULLelse+convert(varchar(23),+a.name+,2)+end

whena.xtype=56thencasewhen+a.name+isnullthenNULLelse+convert(varchar(11),+a.name+)+end

whena.xtype=60thencasewhen+a.name+isnullthenNULLelse+convert(varchar(22),+a.name+)+end

whena.xtype=239thencasewhen+a.name+isnullthenNULLelse+++replace(+a.name+,,)+++end

whena.xtype=108thencasewhen+a.name+isnullthenNULLelse+convert(varchar(+convert(varchar(4),a.xprec+2)+),+a.name+)+end

whena.xtype=231thencasewhen+a.name+isnullthenNULLelse+++replace(+a.name+,,)+++end

whena.xtype=59thencasewhen+a.name+isnullthenNULLelse+convert(varchar(23),+a.name+,2)+end

whena.xtype=58thencasewhen+a.name+isnullthenNULLelse+++convert(varchar(23),+a.name+,121)+++end

whena.xtype=52thencasewhen+a.name+isnullthenNULLelse+convert(varchar(12),+a.name+)+end

whena.xtype=122thencasewhen+a.name+isnullthenNULLelse+convert(varchar(22),+a.name+)+end

whena.xtype=48thencasewhen+a.name+isnullthenNULLelse+convert(varchar(6),+a.name+)+end

--whena.xtype=165thencasewhen+a.name+isnullthenNULLelse+convert(varchar(+convert(varchar(4),a.length*2+2)+),+a.name+)+end

whena.xtype=167thencasewhen+a.name+isnullthenNULLelse+++replace(+a.name+,,)+++end

elseNULL

endascol,a.colid,a.name

fromsyscolumnsawherea.id=object_id(@tablename)anda.xtype189anda.xtype34anda.xtype35anda.xtype36

)torderbycolid

select@sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+)+left(@sqlstr1,len(@sqlstr1)-3)+)from+@tablename

--print@sqlstr

exec(@sqlstr)

setnocountoff

end

GO
因此我们的保存数据方法就是：在删除的动作开始之前，把表数据备份起来，然后留一个空表，在空表上执行“删除”操作。

作者: 只想知道 时间: 2015-1-19 21:08
你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

作者: 变相怪杰 时间: 2015-1-26 12:45
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

作者: 小魔女 时间: 2015-2-4 15:13
其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

作者: 分手快乐 时间: 2015-2-10 02:47
所以你总能得到相应的升级版本，来满足你的需求。

作者: 冷月葬花魂 时间: 2015-2-28 16:16
理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

作者: admin 时间: 2015-3-10 02:33
你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

作者: 第二个灵魂 时间: 2015-3-17 04:09
如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

作者: 不帅 时间: 2015-3-23 18:35
我个人认为就是孜孜不懈的学习

欢迎光临仓酷云 (http://ckuyun.com/)