社区 › MSSQL数据库开发 › MSSQL网站制作之MS SQLSERVER 中怎样失掉表的创立语句

莫相离 发表于 2015-1-16 22:27:52

MSSQL网站制作之MS SQLSERVER 中怎样失掉表的创立语句

MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQLAB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。server|sqlserver|创立|语句
MSSQLSERVER只能失掉存储历程的创立语句,办法以下:

sp_helptextprocedureName

可是常常我们必要失掉表的创立语句,好比说在数据库晋级的时分判别某个表是不是已改动,大概已有一个表存在,但不晓得它的创立语句是甚么,字段有无束缚,有无主键,创立了哪些索引等等.上面我给出一个存储历程,供读者参考.

该存储历程能够失掉你想失掉的一切的表的创立语句,包含和表有关的索引的创立语句.

SQLSERVER2000下的代码

createprocedureSP_GET_TABLE_INFO
@ObjNamevarchar(128)/*Thetabletogeneratesqlscript*/
as

declare@Scriptvarchar(255)
declare@ColNamevarchar(30)
declare@ColIDTinyInt
declare@UserTypesmallint
declare@TypeNamesysname
declare@LengthTinyInt
declare@PrecTinyInt
declare@ScaleTinyInt
declare@StatusTinyInt
declare@cDefaultint
declare@DefaultIDTinyInt
declare@Const_Keyvarchar(255)
declare@IndIDSmallInt
declare@IndStatusInt
declare@Index_Keyvarchar(255)
declare@DBNamevarchar(30)
declare@strPri_Keyvarchar(255)

/*
**Checktoseethethetableexistsandinitialize@objid.
*/
ifnotExists(Selectnamefromsysobjectswherename=@ObjName)
begin
select@DBName=db_name()
raiserror(15009,-1,-1,@ObjName,@DBName)
return(1)
end

createtable#spscript
(
idintIDENTITYnotnull,
ScriptVarchar(255)NOTNULL,
LastLinetinyint
)

declareCursor_ColumnINSENSITIVECURSOR
forSelecta.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status,a.cDefault,
casea.cdefaultwhen0thenelse(selectc.Textfromsyscommentscwherea.cdefault=c.id)endconst_key
fromsyscolumnsa,systypesbwhereobject_name(a.id)=@ObjName
anda.usertype=b.usertypeorderbya.ColID

setnocounton
Select@Script=Createtable+@ObjName+(
Insertinto#spscriptvalues(@Script,0)

/*Getcolumninformation*/
openCursor_Column

fetchnextfromCursor_Columninto@ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key

Select@Script=
while(@@FETCH_STATUS-1)
begin
if(@@FETCH_STATUS-2)
begin
Select@Script=@ColName++@TypeName
if@UserTypein(1,2,3,4)
Select@Script=@Script+(+Convert(char(3),@Length)+)
elseif@UserTypein(24)
Select@Script=@Script+(+Convert(char(3),@Prec)+,
+Convert(char(3),@Scale)+)
else
Select@Script=@Script+
if(@Status&0x80)>0
Select@Script=@Script+IDENTITY(1,1)

if(@Status&0x08)>0
Select@Script=@Script+NULL
else
Select@Script=@Script+NOTNULL
if@cDefault>0
Select@Script=@Script+DEFAULT+@Const_Key
end
fetchnextfromCursor_Columninto@ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
if@@FETCH_STATUS=0
begin
Select@Script=@Script+,
Insertinto#spscriptvalues(@Script,0)
end
else
begin
Insertinto#spscriptvalues(@Script,1)
Insertinto#spscriptvalues(),0)
end
end
CloseCursor_Column
DeallocateCursor_Column

/*Getindexinformation*/
DeclareCursor_IndexINSENSITIVECURSOR
forSelectname,IndID,statusfromsysindexeswhereobject_name(id)=@ObjName
andIndID>0andIndID255orderbyIndID/*增添了对InDid为255的判别*/
OpenCursor_Index
FetchNextfromCursor_Indexinto@ColName,@IndID,@IndStatus
while(@@FETCH_STATUS-1)
begin
if@@FETCH_STATUS-2
begin

declare@iTinyInt
declare@thiskeyvarchar(50)
declare@IndDescvarchar(68)/*stringtobuildupindexdescin*/

Select@i=1
while(@i<=16)
begin
select@thiskey=index_col(@ObjName,@IndID,@i)
if@thiskeyisnull
break

if@i=1
select@Index_Key=index_col(@ObjName,@IndID,@i)
else
select@Index_Key=@Index_Key+,+index_col(@ObjName,@IndID,@i)
select@i=@i+1
end
if(@IndStatus&0x02)>0
Select@Script=Createunique
else
Select@Script=Create
if@IndID=1
select@Script=@Script+clustered


if(@IndStatus&0x800)>0
select@strPri_Key=PRIMARYKEY(+@Index_Key+)
else
select@strPri_Key=

if@IndID>1
select@Script=@Script+nonclustered
Select@Script=@Script+index+@ColName+ON+@ObjName
+(+@Index_Key+)
Select@IndDesc=
/*
**Seeiftheindexisignore_dupkey(0x01).
*/
if@IndStatus&0x01=0x01
Select@IndDesc=@IndDesc+IGNORE_DUP_KEY+,
/*
**Seeiftheindexisignore_dup_row(0x04).
*/
/*if@IndStatus&0x04=0x04*/
/*Select@IndDesc=@IndDesc+IGNORE_DUP_ROW+,*//*2000不在撑持*/
/*
**Seeiftheindexisallow_dup_row(0x40).
*/
if@IndStatus&0x40=0x40
Select@IndDesc=@IndDesc+ALLOW_DUP_ROW+,
if@IndDesc
begin
Select@IndDesc=SubString(@IndDesc,1,DataLength(@IndDesc)-1)
Select@Script=@Script+WITH+@IndDesc
end
/*
**Addthelocationofthedata.
*/
end
if(@strPri_Key=)
Insertinto#spscriptvalues(@Script,0)
else
update#spscriptsetScript=Script+@strPri_KeywhereLastLine=1

FetchNextfromCursor_Indexinto@ColName,@IndID,@IndStatus
end
CloseCursor_Index
DeallocateCursor_Index

SelectScriptfrom#spscript

setnocountoff

return(0)


SQLSERVER6.5下的代码

createprocedureSP_GET_TABLE_INFO
@ObjNamevarchar(128)/*Thetabletogeneratesqlscript*/
as

declare@Scriptvarchar(255)
declare@ColNamevarchar(30)
declare@ColIDTinyInt
declare@UserTypesmallint
declare@TypeNamesysname
declare@LengthTinyInt
declare@PrecTinyInt
declare@ScaleTinyInt
declare@StatusTinyInt
declare@cDefaultint
declare@DefaultIDTinyInt
declare@Const_Keyvarchar(255)
declare@IndIDSmallInt
declare@IndStatusSmallInt
declare@Index_Keyvarchar(255)
declare@SegmentSmallInt
declare@DBNamevarchar(30)
declare@strPri_Keyvarchar(255)

/*
**Checktoseethethetableexistsandinitialize@objid.
*/
ifnotExists(Selectnamefromsysobjectswherename=@ObjName)
begin
select@DBName=db_name()
raiserror(15009,-1,-1,@ObjName,@DBName)
return(1)
end

createtable#spscript
(
idintIDENTITYnotnull,
ScriptVarchar(255)NOTNULL,
LastLinetinyint
)

declareCursor_ColumnINSENSITIVECURSOR
forSelecta.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status,a.cDefault,
casea.cdefaultwhen0thenelse(selectcasec.textwhen"()"then"()"elsec.textend
fromsyscommentscwherea.cdefault=c.id)endconst_key
fromsyscolumnsa,systypesbwhereobject_name(a.id)=@ObjName
anda.usertype=b.usertypeorderbya.ColID

setnocounton
Select@Script=Createtable+@ObjName+(
Insertinto#spscriptvalues(@Script,0)

/*Getcolumninformation*/
openCursor_Column

fetchnextfromCursor_Columninto@ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key

Select@Script=
while(@@FETCH_STATUS-1)
begin
if(@@FETCH_STATUS-2)
begin
Select@Script=@ColName++@TypeName
if@UserTypein(1,2,3,4)
Select@Script=@Script+(+Convert(char(3),@Length)+)
elseif@UserTypein(24)
Select@Script=@Script+(+Convert(char(3),@Prec)+,
+Convert(char(3),@Scale)+)
else
Select@Script=@Script+
if(@Status&0x80)>0
Select@Script=@Script+IDENTITY(1,1)

if(@Status&0x08)>0
Select@Script=@Script+NULL
else
Select@Script=@Script+NOTNULL
if@cDefault>0
Select@Script=@Script+DEFAULT+@Const_Key
end
fetchnextfromCursor_Columninto@ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
if@@FETCH_STATUS=0
begin
Select@Script=@Script+,
Insertinto#spscriptvalues(@Script,0)
end
else
begin
Insertinto#spscriptvalues(@Script,1)
Insertinto#spscriptvalues(),0)
end
end
CloseCursor_Column
DeallocateCursor_Column

/*Getindexinformation*/
DeclareCursor_IndexINSENSITIVECURSOR
forSelectname,IndID,status,Segmentfromsysindexeswhereobject_name(id)=@ObjName
andIndID>0andIndID255orderbyIndID
OpenCursor_Index
FetchNextfromCursor_Indexinto@ColName,@IndID,@IndStatus,@Segment
while(@@FETCH_STATUS-1)
begin
if@@FETCH_STATUS-2
begin

declare@iTinyInt
declare@thiskeyvarchar(50)
declare@IndDescvarchar(68)/*stringtobuildupindexdescin*/

Select@i=1
while(@i<=16)
begin
select@thiskey=index_col(@ObjName,@IndID,@i)
if@thiskeyisnull
break

if@i=1
select@Index_Key=index_col(@ObjName,@IndID,@i)
else
select@Index_Key=@Index_Key+,+index_col(@ObjName,@IndID,@i)
select@i=@i+1
end
if(@IndStatus&0x02)>0
Select@Script=Createunique
else
Select@Script=Create
if@IndID=1
select@Script=@Script+clustered


if(@IndStatus&0x800)>0
select@strPri_Key=PRIMARYKEY(+@Index_Key+)
else
select@strPri_Key=

if@IndID>1
select@Script=@Script+nonclustered
Select@Script=@Script+index+@ColName+ON+@ObjName
+(+@Index_Key+)
Select@IndDesc=
/*
**Seeiftheindexisignore_dupkey(0x01).
*/
if@IndStatus&0x01=0x01
Select@IndDesc=@IndDesc+IGNORE_DUP_KEY+,
/*
**Seeiftheindexisignore_dup_row(0x04).
*/
if@IndStatus&0x04=0x04
Select@IndDesc=@IndDesc+IGNORE_DUP_ROW+,
/*
**Seeiftheindexisallow_dup_row(0x40).
*/
if@IndStatus&0x40=0x40
Select@IndDesc=@IndDesc+ALLOW_DUP_ROW+,
if@IndDesc
begin
Select@IndDesc=SubString(@IndDesc,1,DataLength(@IndDesc)-1)
Select@Script=@Script+WITH+@IndDesc
end
/*
**Addthelocationofthedata.
*/
if@Segment1
select@Script=@Script+ON+name
fromsyssegments
wheresegment=@Segment
end
if(@strPri_Key=)
Insertinto#spscriptvalues(@Script,0)
else
update#spscriptsetScript=Script+@strPri_KeywhereLastLine=1

FetchNextfromCursor_Indexinto@ColName,@IndID,@IndStatus,@Segment
end
CloseCursor_Index
DeallocateCursor_Index

SelectScriptfrom#spscriptorderbyid

setnocountoff

return(0)



mysql使用内部操作字符集gbk来进行操作，即执行"SELECT*FROMtestWHEREname=xxxor1=1/*LIMIT1";从而注入成功

简单生活 发表于 2015-1-17 23:41:46

我们学到了什么？思考问题的时候从表的角度来思考问

飘灵儿 发表于 2015-1-21 10:59:15

多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

海妖 发表于 2015-1-30 16:10:48

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

爱飞 发表于 2015-2-6 13:59:46

如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。

小女巫 发表于 2015-2-16 09:49:21

多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

兰色精灵 发表于 2015-3-5 03:42:55

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

透明 发表于 2015-3-11 23:11:39

始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。

飘飘悠悠 发表于 2015-3-28 22:53:08

无法深入到数据库系统层面去了解和探究

查看完整版本: MSSQL网站制作之MS SQLSERVER 中怎样失掉表的创立语句