Get DDL for any SQL 2000 table.doc

Get DDL for any SQL 2000 table
behind the scenes it's smart, complicated code. usage upfront is easy:
exec sp_GetDDL YourTableName
-- or
exec sp_GetDDL 'schemaname.tablename'
-- or
exec sp_GetDDL '[schemaname].[tablename]'
it produces well formatted CREATE TABLE scripts like this: note that some detail went into the spacing to make everything have a sharp appearance
CREATE TABLE [dbo].[TBSTATE] ( 
[STATETBLKEY]  INT                              NOT NULL,
[INDEXTBLKEY]  INT                              NOT NULL,
[STATECODE]    CHAR(2)                          NOT NULL,
[STATENAME]    VARCHAR(50)                      NOT NULL,
[FIPS]         CHAR(3)                              NULL,
CONSTRAINT   [PK__TBSTATE__17A421EC]                   PRIMARY KEY CLUSTERED    (STATETBLKEY),
CONSTRAINT   [STATECODEUNIQUE]                         UNIQUE      NONCLUSTERED (STATECODE))

-- 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 you've enhanced.The feedback you give will be 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 database's sys.indexes, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject '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(8000),
@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 [name] = @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 @FINALSQL = '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.
--##############################################################################
SELECT
@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 '('
+ CONVERT(VARCHAR,syscolumns.prec)
+ ','
+ CONVERT(VARCHAR,syscolumns.xscale)
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,syscolumns.prec)
+ ','
+ CONVERT(VARCHAR,syscolumns.xscale)))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE FLOAT(53)
WHEN TYPE_NAME(syscolumns.xusertype) IN ('float','real')
THEN
--addition: if 53, no need to specifically say (53), otherwise display it
CASE
WHEN syscolumns.prec = 53
THEN SPACE(11 - LEN(CONVERT(VARCHAR,syscolumns.prec)))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,syscolumns.prec)
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,syscolumns.prec)))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie VARCHAR(40)
WHEN TYPE_NAME(xusertype) IN ('char','varchar')
THEN CASE
WHEN length = -1
THEN '(8000)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,syscolumns.length)))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,syscolumns.length)
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,syscolumns.length)))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie NVARCHAR(40)
WHEN TYPE_NAME(syscolumns.xusertype) IN ('nchar','nvarchar')
THEN CASE
WHEN prec = -1
THEN '(8000)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,syscolumns.prec)))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,syscolumns.prec)
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,syscolumns.prec)))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie datetime
WHEN TYPE_NAME(syscolumns.xusertype) IN ('datetime','money','text','image')
THEN SPACE(18 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ ' '
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE INT
ELSE SPACE(16 - LEN(TYPE_NAME(syscolumns.xusertype)))
+ CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , name , 'IsIdentity' ) = 0
THEN ' '
ELSE ' IDENTITY('
+ CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
+ SPACE(2)
+ CASE
WHEN syscolumns.isnullable = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
+ CASE
WHEN syscolumns.[cdefault] = 0
THEN ''
ELSE ' DEFAULT ' + ISNULL(def.text ,'')
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
--##############################################################################
-- COLLATE STATEMENTS
-- personally i do not like collation statements,
-- but included here to make it easy on those who do
--##############################################################################
/*
+ CASE
WHEN collation IS NULL
THEN ''
ELSE ' COLLATE ' + syscolumns.collation
END
*/
END --iscomputed
+ ','
FROM syscolumns
LEFT OUTER JOIN syscomments DEF
on syscolumns.cdefault = DEF.id
Where syscolumns.id=@TABLE_ID
ORDER BY syscolumns.colid
--##############################################################################
--used for formatting the rest of the constraints:
--##############################################################################
SELECT
@STRINGLEN = MAX(LEN([name])) + 1
FROM sysobjects
--##############################################################################
--PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax
--##############################################################################
--2000 annoyance: could not use cross apply or for xml:
DECLARE @Results TABLE (
[schema_id] int,
[schema_name] varchar(255),
[object_id] int,
[object_name] varchar(255),
[index_id] int,
[index_name] varchar(255),
[Rows] int,
[SizeMB] decimal(19,3),
[IndexDepth] int,
[type] int,
[type_desc] varchar(30),
[fill_factor] int,
[is_unique] int,
[is_primary_key] int ,
[is_unique_constraint] int,
[indexcolumn] varchar(50),
[colid] int,
[index_columns_key] varchar(6000),
[index_columns_include] varchar(3))
INSERT INTO @Results
select
sysobjects.uid AS schema_id,
user_name(uid) AS schema_name,
sysobjects.id AS object_id,
sysobjects.name AS object_name,
sysindexes.indid as index_id,
--
ISNULL(sysindexes.name, '---') AS index_name,
sysindexes.Rows,
0 AS SizeMB,
IndexProperty(sysobjects.id, sysindexes.name, 'IndexDepth') AS IndexDepth,
CASE
WHEN sysindexes.indid = 0 then 0
WHEN sysindexes.indid = 1 then 1
ELSE 2
END AS type,
CASE
WHEN INDEXPROPERTY (sysindexes.ID,sysindexes.NAME,'ISCLUSTERED') = 1
THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END AS type_desc,
INDEXPROPERTY (sysindexes.ID,sysindexes.NAME,'INDEXFILLFACTOR') AS fill_factor,
INDEXPROPERTY (sysindexes.ID,sysindexes.NAME,'ISUNIQUE') AS [is_unique],
INDEXPROPERTY (sysindexes.ID,sysindexes.NAME,'ISCLUSTERED') AS [is_primary_key],
CASE
WHEN sysobjects.xtype='UQ'
THEN 1
ELSE 0
END AS is_unique_constraint,
syscolumns.name AS indexcolumn,
sysindexkeys.colid,
'' AS index_columns_key,
'---' AS index_columns_include

from sysindexes
inner join sysobjects on sysindexes.id = sysobjects.id
inner join sysindexkeys
on sysindexes.id = sysindexkeys.id
and sysindexes.indid = sysindexkeys.indid
INNER JOIN syscolumns