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))
-- USAGE: exec sp_GetDDL whatever1    
--   or   exec sp_GetDDL 'schemaname.tablename' 
--   or   exec sp_GetDDL '[schemaname].[table name]'      
--#############################################################################    
-- copyright 2004-2009 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.    
-- http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2000_V2.txt
-- 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 
                               ON   sysindexkeys.id=syscolumns.id 
                               AND  sysindexkeys.colid=syscolumns.colid
WHERE sysindexes.INDID > 0 
AND sysindexes.INDID < 255 
AND (sysindexes.STATUS & 64)=0
and user_name(uid)      LIKE CASE WHEN @SCHEMANAME='' THEN user_name(uid)  ELSE @SCHEMANAME END
AND sysobjects.name     LIKE CASE WHEN @TBLNAME=''    THEN sysobjects.name ELSE @TBLNAME END
ORDER BY user_name(uid) , sysobjects.name, sysindexes.name,sysindexkeys.colid 

--now update the column to have all the names

declare @indexname varchar(200), @name varchar(200)
--select @name ='' ,@indexname =category from test where id = 1 
update t 
set @name = case when @indexname = index_name then @name +','+ indexcolumn else indexcolumn end , index_columns_key = @name, @indexname = index_name
from @results t

update @results
set index_columns_key = x.index_columns_key
from @results t
join (select max(index_columns_key)index_columns_key, index_name from @results group by index_name)x
on x.index_name = t.index_name
--cleanup the extra rows
delete from t
From @results t
inner join (select index_name,MIN(colid) AS colid FROM @results GROUP BY index_name)x
ON T.index_name = X.index_name
WHERE T.index_name = X.index_name 
AND T.colid <> x.colid
--@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results:
SET @CONSTRAINTSQLS = ''
SET @INDEXSQLS      = ''
  
--############################################################################## 
--constriants
--############################################################################## 
SELECT  @CONSTRAINTSQLS = @CONSTRAINTSQLS + 
CASE 
  WHEN is_primary_key = 1 or is_unique = 1
  THEN CHAR(13)
       + 'CONSTRAINT   [' + index_name + '] ' 
       + SPACE(@STRINGLEN - LEN(index_name))
       + CASE  WHEN is_primary_key = 1 THEN ' PRIMARY KEY ' ELSE CASE  WHEN is_unique = 1     THEN ' UNIQUE      '      ELSE '' END END 
       + type_desc + CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE '   ' END
       + ' (' + index_columns_key + ')' 
       + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
       + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END
  ELSE ''
END + ',' 
from @RESULTS
where [type_desc] != 'HEAP'
  AND is_primary_key = 1 or is_unique = 1
order by is_primary_key desc,is_unique desc
--############################################################################## 
--indexes
--############################################################################## 
SELECT @INDEXSQLS = @INDEXSQLS + 
CASE 
  WHEN is_primary_key = 0 or is_unique = 0
  THEN CHAR(13)
       + 'CREATE INDEX [' + index_name + '] '
       + SPACE(@STRINGLEN - LEN(index_name))
       + ' ON [' + [object_name] + ']'
       + ' (' + index_columns_key + ')' 
       + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
       + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END

END 
from @RESULTS
where [type_desc] != 'HEAP'
  AND is_primary_key = 0 AND is_unique = 0
order by is_primary_key desc,is_unique desc
--##############################################################################  
--CHECK Constraints
--############################################################################## 
  SET @CHECKCONSTSQLS = ''
  SELECT
    @CHECKCONSTSQLS = @CHECKCONSTSQLS 
    + CHAR(13)
    + ISNULL('CONSTRAINT   [' + sysobjects.name + '] '
    + SPACE(@STRINGLEN - LEN(sysobjects.name)) 
    + ' CHECK ' + ISNULL(syscomments.text,'')
    + ',','')
  FROM sysobjects
    INNER JOIN syscomments ON sysobjects.id = syscomments.id 
  WHERE sysobjects.xtype = 'C' 
    AND sysobjects.parent_obj = @TABLE_ID
--##############################################################################    
--FOREIGN KEYS 
--############################################################################## 
  SET @FKSQLS = '' ;    
  SELECT 
    @FKSQLS=@FKSQLS 
    + CHAR(13) 
    + 'CONSTRAINT   [' + OBJECT_NAME(constid) +']'
    + SPACE(@STRINGLEN - LEN(OBJECT_NAME(constid) )) 
    + '  FOREIGN KEY ('   + COL_NAME(fkeyid,fkey)
    + ') REFERENCES '    + OBJECT_NAME(rkeyid) 
    +'(' + COL_NAME(rkeyid,rkey) + '),' 
  from sysforeignkeys 
  WHERE fkeyid = @TABLE_ID
--##############################################################################  
--RULES
--############################################################################## 
 SET @RULESCONSTSQLS = ''
 SELECT 
   @RULESCONSTSQLS = @RULESCONSTSQLS 
   + ISNULL(
            CHAR(13) 
            + 'if not exists(SELECT NAME FROM SYSOBJECTS WHERE XTYPE=''R'' AND NAME = ''[' + sysobjects.name + ']'')' + CHAR(13)
            + syscomments.text  + CHAR(13)
            + 'EXEC sp_binderule  [' + sysobjects.name + '], ''[' + OBJECT_NAME(syscolumns.id) + '].[' + syscolumns.name + ']''' + CHAR(13) + 'GO' ,'')
 from syscolumns 
   inner join sysobjects 
     on syscolumns.domain = sysobjects.id
   inner join syscomments 
     on sysobjects.id = syscomments.id 
 where sysobjects.xtype = 'R' 
   and  syscolumns.domain <> 0
   and syscolumns.id = @TABLE_ID
--##############################################################################  
--TRIGGERS
--############################################################################## 
 SET @TRIGGERSTATEMENT = ''    
 SELECT    
   @TRIGGERSTATEMENT = @TRIGGERSTATEMENT +  CHAR(13) + [text] + CHAR(13) + 'GO'    
 FROM syscomments    
 WHERE id IN(SELECT    
               id    
             FROM sysobjects    
             WHERE xtype      = 'TR'    
               AND parent_obj = @TABLE_ID)  
 
--##############################################################################  
--FINAL CLEANUP AND PRESENTATION  
--##############################################################################  
--at this point, there is a trailing comma, or it blank  
  SELECT 
    @FINALSQL = @FINALSQL
                + @CONSTRAINTSQLS
                + @CHECKCONSTSQLS
                + @FKSQLS
--note that this trims the trailing comma from the end of the statements  
  SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;  
  SET @FINALSQL = @FINALSQL + ')' + CHAR(13) ; 

SELECT @FINALSQL 
     + @INDEXSQLS
     + @RULESCONSTSQLS
     + @TRIGGERSTATEMENT

END

Rate

2 (1)

Share

Share

Rate

2 (1)