《Get DDL for any SQL 2000 table（精品）》由会员分享，可在线阅读，更多相关《Get DDL for any SQL 2000 table（精品）（12页珍藏版）》请在金锄头文库上搜索。
1、Get DDL for any SQL 2000 tablebehind the scenes its smart, complicated code. usage upfront is easy:exec sp_GetDDL YourTableName- orexec sp_GetDDL schemaname.tablename- orexec sp_GetDDL schemaname.tablenameit produces well formatted CREATE TABLE scripts like this: note that some detail went into the
2、spacing to make everything have a sharp appearanceCREATETABLEdbo.TBSTATE(STATETBLKEYINTNOTNULL,INDEXTBLKEYINTNOTNULL,STATECODECHAR(2)NOTNULL,STATENAMEVARCHAR(50)NOTNULL,FIPSCHAR(3)NULL,CONSTRAINTPK_TBSTATE_17A421ECPRIMARYKEYCLUSTERED(STATETBLKEY),CONSTRAINTSTATECODEUNIQUEUNIQUENONCLUSTERED(STATECODE
3、)- You can use this however you like.this script is not rocket science, but it took a bit of work to create.- the only thing that I ask - is that if you adapt my procedure or make it better, to simply send me a copy of it, - so I can learn from the things youve enhanced.The feedback you give will be
4、 what makes - it worthwhile to me, and will be fed back to the SQL community. - add this to your toolbox of helpful scripts.-# -if you are going to put this in MASTER, and want it to be able to query -each databases sys.indexes, you MUST mark it as a system procedure:-EXECUTE sp_ms_marksystemobject
5、sp_GetDDL-# CREATE PROCEDURE dbo.sp_GetDDL TBL VARCHAR(255) AS BEGIN SET NOCOUNT ON DECLARE TBLNAME VARCHAR(200), SCHEMANAME VARCHAR(255), STRINGLEN INT, TABLE_ID INT, FINALSQL VARCHAR(8000), CONSTRAINTSQLS VARCHAR(8000), CHECKCONSTSQLS VARCHAR(8000), RULESCONSTSQLS VARCHAR(8000), FKSQLS VARCHAR(800
6、0), TRIGGERSTATEMENT VARCHAR(8000), INDEXSQLS VARCHAR(8000)-# - INITIALIZE -# -SET TBL = DBO.WHATEVER1 -does the tablename contain a schema? SELECT SCHEMANAME = ISNULL(PARSENAME(TBL,2),dbo) , TBLNAME = PARSENAME(TBL,1) SELECT TABLE_ID = id FROM sysobjects WHERE xtype = U AND name dtproperties AND na
7、me = TBLNAME AND uid = user_id(SCHEMANAME) ; -# - Check If TableName is Valid -# IF ISNULL(TABLE_ID,0) = 0 BEGIN SET FINALSQL = Table object + SCHEMANAME + . + UPPER(TBLNAME) + does not exist in Database + db_name() + SELECT FINALSQL; RETURN 0 END -# - Valid Table, Continue Processing -# SELECT FINA
8、LSQL = CREATE TABLE + SCHEMANAME + . + UPPER(TBLNAME) + ( SELECT TABLE_ID = OBJECT_ID(TBLNAME) SELECT STRINGLEN = MAX(LEN(syscolumns.name) + 1 FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id AND sysobjects.id = TABLE_ID; -# -Get the columns, their definitions and defaults.-# S
9、ELECT FINALSQL = FINALSQL + CASE WHEN syscolumns.ISCOMPUTED = 1 THEN CHAR(13) + + UPPER(syscolumns.name) + + SPACE(STRINGLEN - LEN(syscolumns.name) + AS + UPPER(syscolumns.name) ELSE CHAR(13) + + UPPER(syscolumns.name) + + SPACE(STRINGLEN - LEN(syscolumns.name) + UPPER(TYPE_NAME(syscolumns.xusertype) + CASE -IE NUMERIC(10,2) WHEN TYPE_NAME(syscolumns.xusertype) IN (decimal,numeric) THEN ( + CONV
