locked
sp_helptext Equivalent for Tables? RRS feed

  • Question

  • Hello Everybody, Please help me out:

     

    Is there a system stored procedure for generating the sql statement that would create an existing table? For example, tblCustomers ("create table tblCustomers ( .....)")

     

    I know i can use sp_helptext for views etc; i desire the equivalent for tables.

    sp_columns is not adequate either.

     

    please help! thanks in advance.

    Monday, May 14, 2007 9:22 PM

Answers

  • I kept the same thing in my mind to write this code for long time..

    Once I completed with it... Try this.. You can add your enhancements also....

     

    Code Snippet

    Create  Proc sp_helpTable

    (

                @TableName Nvarchar(2000)

    )

    as

    Create Table #Text

    (

                Source NVarchar(4000)

    )

     

    Insert Into #Text(Source) Values ('Create Table ' + @TableName + '(');

     

    Insert Into #Text(Source)

    Select

     '[' + C.Name + '] ' +  Ty.name + Case When C.Scale Is NULL Then '(' + Cast(C.Length as Varchar) + ') ' Else '' End +

                Case When C.IsNullable =0 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End 

                + Case When C.Colstat & 1 = 1 Then ' Identity(' +  Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') '  Else '' End

                + Isnull(' Constraint ' + ChkCon.Name + ' Check ' + comments.Text ,'')

                + Isnull(' Default ' + defcomments.text ,'') + ','

    From

     Sysobjects T

      Join Syscolumns C on T.id = C.Id

      Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'

      Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id

                            And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder

                Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1

      Left Outer Join sysobjects def On def.parent_obj = T.Id

                            And def.xtype= 'D' And def.Info = C.Colorder

                Left Outer Join syscomments defcomments ON defcomments.id = def.id 

     

    Where

     T.Type='U'

     And T.Name=@TableName

    Order By

     T.Name,

     C.Colorder

     

     

    Insert Into #Text(Source)

    Select

                'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End  + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End  +  '(' +  dbo.GetAllIndexedColumns(@TableName, 2)  + '),'

    From

                sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)

                            and ind.name = object_name(tbl.id)

                            and xtype in ('PK', 'UQ')

     

    Insert Into #Text(Source)

    select

                'Constraint [' + tbl.name + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' + 

                object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'

    from    

                sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)

                                        and fk.constid = tbl.id

                                        and xtype in ('F')

     

    Declare @Source as Nvarchar(4000);

    Select @Source = Source From #Text;

    Update #Text

    Set

                Source = Substring(Source,1,Len(Source)-1)

    Where

                Source = @Source;

     

    Insert Into #Text(Source) values (')');

     

    Select Source From #Text

     

     

     

    Tuesday, May 15, 2007 5:13 AM

All replies

  • Unfortunately, there is none that I am aware of.  However, it really isn't all that hard to construct the create table statement from using the INFORMATION_SCHEMA.COLUMNS view.  It has 'almost' everything you need to create the table.  If you want to get fancy and create the default constraints and such, you'll need to join with a couple of other tables, but its not too much.
    Monday, May 14, 2007 11:19 PM
  • Nope, I suggested it here as an improvement for object_definition (which is a 2005 version of sp_helptext that you can use as select object_definition(object_id) to get the text.:

     

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256665

    Tuesday, May 15, 2007 3:41 AM
  • I kept the same thing in my mind to write this code for long time..

    Once I completed with it... Try this.. You can add your enhancements also....

     

    Code Snippet

    Create  Proc sp_helpTable

    (

                @TableName Nvarchar(2000)

    )

    as

    Create Table #Text

    (

                Source NVarchar(4000)

    )

     

    Insert Into #Text(Source) Values ('Create Table ' + @TableName + '(');

     

    Insert Into #Text(Source)

    Select

     '[' + C.Name + '] ' +  Ty.name + Case When C.Scale Is NULL Then '(' + Cast(C.Length as Varchar) + ') ' Else '' End +

                Case When C.IsNullable =0 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End 

                + Case When C.Colstat & 1 = 1 Then ' Identity(' +  Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') '  Else '' End

                + Isnull(' Constraint ' + ChkCon.Name + ' Check ' + comments.Text ,'')

                + Isnull(' Default ' + defcomments.text ,'') + ','

    From

     Sysobjects T

      Join Syscolumns C on T.id = C.Id

      Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'

      Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id

                            And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder

                Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1

      Left Outer Join sysobjects def On def.parent_obj = T.Id

                            And def.xtype= 'D' And def.Info = C.Colorder

                Left Outer Join syscomments defcomments ON defcomments.id = def.id 

     

    Where

     T.Type='U'

     And T.Name=@TableName

    Order By

     T.Name,

     C.Colorder

     

     

    Insert Into #Text(Source)

    Select

                'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End  + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End  +  '(' +  dbo.GetAllIndexedColumns(@TableName, 2)  + '),'

    From

                sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)

                            and ind.name = object_name(tbl.id)

                            and xtype in ('PK', 'UQ')

     

    Insert Into #Text(Source)

    select

                'Constraint [' + tbl.name + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' + 

                object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'

    from    

                sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)

                                        and fk.constid = tbl.id

                                        and xtype in ('F')

     

    Declare @Source as Nvarchar(4000);

    Select @Source = Source From #Text;

    Update #Text

    Set

                Source = Substring(Source,1,Len(Source)-1)

    Where

                Source = @Source;

     

    Insert Into #Text(Source) values (')');

     

    Select Source From #Text

     

     

     

    Tuesday, May 15, 2007 5:13 AM
  • Hi,

     

    The OBJECT_DEFINITION  returns the tsql code for constraints, udf's, views, procedures, etc but unfortunately tables are not on the list.

     

    I hope the tables will be added to the list.

     

    I traced the sql codes run on the server by using the profiler when I scripted the create table on the SSMS, a huge list of selects works so a little bit complex to be run during runtime dynamically.

     

     

    Tuesday, May 15, 2007 6:11 AM
  • Thanks everybody for helping out. I will give sp_helpTable a try and report back to the forum.

     

    Cheers!

    Tuesday, May 15, 2007 3:31 PM
  • Here I got a script that can help

     

    Just run the stored procedure I made and you will get the basic structure of the table:

     

    CREATE PROCEDURE sp_helpTable

    @sTable_name VARCHAR(100)

    AS

    SET NOCOUNT ON

    --TEST ZONE--

    --DECLARE @sTable_name VARCHAR(100)

    --SELECT @sTable_name = 'BTST_CAT_PURPOSE_REMITTANCE'

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

    DECLARE @nSeqId SMALLINT,

    @nMaxSeqId INT,

    @nMaxInformation INT,

    @nSeq_Col_Id INT,

    @nMaxSeq_Col_Id INT,

    @nMaxOrdinal_Position INT

     

    CREATE TABLE #TABLES_DATABASE(

    Seq_Id INT IDENTITY(1,1),

    Table_name NVARCHAR(100),

    )

     

    CREATE TABLE #COLUMNS_TABLES(

    Seq_Id INT IDENTITY(1,1),

    Column_name NVARCHAR(100),

    Ordinal_Position INT

    )

     

    CREATE TABLE #INFORMATION(

    Seq_Id INT IDENTITY(1,1),

    Create_table NVARCHAR(100),

    Columns_aray NVARCHAR(1000),

    Close_create NVARCHAR(20))

     

    INSERT INTO #TABLES_DATABASE(Table_name)

    SELECT TABLE_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = @sTable_name

    GROUP BY TABLE_NAME

     

    SELECT @nSeqId = 1,

    @nMaxSeqId = MAX(Seq_Id)

    FROM #TABLES_DATABASE

     

    WHILE @nSeqId<=@nMaxSeqId

    BEGIN

     

    INSERT INTO #INFORMATION(Create_table)

    SELECT 'CREATE TABLE ' + A.TABLE_SCHEMA + '.' + A.TABLE_NAME + ' (' + NCHAR(10)

    FROM INFORMATION_SCHEMA.COLUMNS A

    INNER JOIN #TABLES_DATABASE B

    ON A.TABLE_NAME=B.Table_name

    WHERE B.Seq_Id = @nSeqId

    GROUP BY A.TABLE_SCHEMA, A.TABLE_NAME

     

    SELECT @nMaxInformation = MAX(Seq_Id) FROM #INFORMATION

     

    INSERT INTO #COLUMNS_TABLES(Column_name,Ordinal_Position)

    SELECT COLUMN_NAME,ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (SELECT Table_name FROM #TABLES_DATABASE WHERE Seq_Id = @nSeqId)

     

    SELECT @nSeq_Col_Id = 1,

    @nMaxSeq_Col_Id = MAX(Seq_Id),

    @nMaxOrdinal_Position = MAX(Ordinal_Position)

    FROM #COLUMNS_TABLES

     

    WHILE @nSeq_Col_Id<=@nMaxSeq_Col_Id

    BEGIN

     

    UPDATE #INFORMATION

    SET Columns_aray = Columns_aray + (SELECT NCHAR(9) + A.COLUMN_NAME + ' ' + A.DATA_TYPE +

    CASE DATA_TYPE

    WHEN 'char' THEN '(' + CONVERT(VARCHAR,A.CHARACTER_MAXIMUM_LENGTH) + ')'

    WHEN 'varchar' THEN '(' + CONVERT(VARCHAR,A.CHARACTER_MAXIMUM_LENGTH) + ')'

    WHEN 'nvarchar' THEN '(' + CONVERT(VARCHAR,A.CHARACTER_MAXIMUM_LENGTH) + ')'

    ELSE ''

    END

    +

    CASE MAX(ORDINAL_POSITION)

    WHEN @nMaxOrdinal_Position THEN ''

    ELSE ',' + NCHAR(10)

    END

    FROM INFORMATION_SCHEMA.COLUMNS A

    INNER JOIN #COLUMNS_TABLES B

    ON A.COLUMN_NAME = B.Column_name

    INNER JOIN #TABLES_DATABASE C

    ON A.TABLE_NAME=C.Table_name

    AND C.Seq_Id = @nSeqId

    WHERE B.Seq_Id = @nSeq_Col_Id

    GROUP BY A.COLUMN_NAME,A.DATA_TYPE,A.CHARACTER_MAXIMUM_LENGTH)

    WHERE Seq_Id = @nMaxInformation

     

    SELECT @nSeq_Col_Id = @nSeq_Col_Id + 1

     

    END

     

    TRUNCATE TABLE #COLUMNS_TABLES

     

    UPDATE #INFORMATION

    SET Close_create = NCHAR(10) + ')'

    WHERE Seq_Id = @nMaxInformation

     

    SELECT @nSeqId = @nSeqId + 1

     

    END

     

    SELECT Create_table + Columns_aray + Close_create + NCHAR(10) + 'GO'

    FROM #INFORMATION

    ORDER BY Create_table

     

    DROP TABLE #TABLES_DATABASE

    DROP TABLE #INFORMATION

    DROP TABLE #COLUMNS_TABLES

     

    SET NOCOUNT OFF

    GO

     

     

    You can change the stored procedure if you need more information from table

     

    NOTE: THIS SCRIPT ONLY APPLIES ON SQL 2005

    Friday, April 11, 2008 5:03 PM
  • What's the dbo.GetAllIndexedColumns procedure supposed to do?
    Monday, July 16, 2012 10:44 PM