A little Help

  • leaders_j

    SSC-Addicted

    Points: 485

    Base on the code bellow, it actually works but the fact is that it does not show the complete create statement. if there anything you can do to help, I will be very grateful. The code is as follows

    CREATE PROCEDURE sp_ExportTables

    -- USAGE: sp_ExportTables gmproj

    @table_name varchar(32)

    as

    begin

    Create Table #CreateStatements (uid int identity(1,1),Info text)

    DECLARE @table_id int,

    @CurrColumn int,

    @MaxColumn int,

    @CreateStatement varchar(8000),

    @ColumnTypeName varchar(255),

    @uid int,

    @i int,

    @primary_key_field varchar(50)

    select @table_id=id from sysobjects where xtype='U' and [name] <> 'dtproperties' and [name] = @table_name

    /*Since a table can have only one Primary key, get the column name for this table(if any) that is the PK*/

    select @primary_key_field = convert(varchar(32),c.name)

    from

    sysindexes i, syscolumns c, sysobjects o, syscolumns c1

    where

    o.id = @table_id

    and o.id = c.id

    and o.id = i.id

    and (i.status & 0x800) = 0x800

    and c.name = index_col (@table_name, i.indid, c1.colid)

    and c1.colid <= i.keycnt

    and c1.id = @table_id

    Select @CreateStatement = CHAR(13) + 'CREATE TABLE [' + [name] + '] ( ' from SYSOBJECTS WHERE ID=@TABLE_ID

    --For Each Column

    Select @CurrColumn=Min(colid),@MaxColumn = Max(colid) from syscolumns where id= @table_id

    --Select * from syscolumns where id=1511676433

    while @currColumn <= @MaxColumn

    begin

    --print @currColumn

    Declare @UQIndex int, @DefaultValue nvarchar(4000)

    set @DefaultValue = null

    select @DefaultValue=text from syscomments where id=

    (select constid from sysconstraints where id=@table_id and colid=@currColumn)

    --Process different Column Types differently

    SELECT @CreateStatement = @CreateStatement + CHAR(13) + '[' + [name] + '] ' + type_name(xtype) +

    case

    --ie numeric(10,2)

    WHEN type_name(xtype) IN ('decimal','numeric') THEN

    ' ('+ convert(varchar,prec) + ',' + convert(varchar,length) + ')'

    + case when autoval is null then '' else ' IDENTITY(1,1)' end

    + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END

    --ie float(53)

    WHEN type_name(xtype) IN ('float','real') THEN

    ' ('+ convert(varchar,prec) + ')'

    + case when autoval is null then '' else ' IDENTITY(1,1)' end

    + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END

    --ie varchar(40)

    WHEN type_name(xtype) IN ('char','varchar','nchar','nvarchar') THEN

    ' ('+ convert(varchar,length) + ')'

    + case when autoval is null then '' else ' IDENTITY(1,1)' end

    + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END

    --ie int

    ELSE

    + case when autoval is null then '' else ' IDENTITY(1,1)' end

    + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END

    end

    --code to determine if 'PRIMARY KEY'

    + CASE when syscolumns.name = @primary_key_field THEN ' PRIMARY KEY' else '' END

    + CASE when @DefaultValue is null then ''

    ELSE

    CASE

    WHEN type_name(xtype) IN ('decimal','numeric','float','real','bigint','int','smallint','tinyint','money','smallmoney') THEN

    ' DEFAULT ' + convert(varchar,@DefaultValue)

    ELSE

    ' DEFAULT ' + convert(varchar,@DefaultValue)

    END

    END

    + ',' from syscolumns where id=@table_id and colid=@CurrColumn

    Select @CurrColumn = @CurrColumn + 1

    end

    insert into #CreateStatements(Info) values(@CreateStatement)

    SELECT @CreateStatement=''

    select @uid=@@IDENTITY

    --CODE TO ADD ALL THE FOREIGN KEYS TO THE BOTTOM OF THE STATEMENT

    declare @cursorID int

    declare c1 cursor for SELECT fkeyid from sysforeignkeys where fkeyid=@table_id

    open c1

    fetch next from c1 into @cursorID

    SELECT @CreateStatement=@CreateStatement +

    (select + CHAR(13) +'FOREIGN KEY (' + [syscolumns].[name] + ') REFERENCES ' from syscolumns where id=fkeyid and colid =fkey) +

    (select (SELECT distinct [sysobjects].[name] from sysobjects where id=rkeyid) + '(' + [syscolumns].[name] + '),' from syscolumns where id=rkeyid and colid =rkey)

    from sysforeignkeys where fkeyid=@table_id

    close c1

    deallocate c1

    --CODE TO ADD ALL THE UNIQUE CONSTRAINTS TO THE BOTTOM OF THE DEFINITION.

    declare c1 cursor for select id from sysobjects where xtype='UQ' and parent_obj=@table_id

    open c1

    fetch next from c1 into @cursorID

    --adapted shamelessly from sp_helpconstraints

    while @@fetch_status >= 0

    begin

    declare @indid smallint

    SELECT @indid = indid,@CreateStatement=@CreateStatement + CHAR(13) + 'CONSTRAINT ' + object_name(@cursorID) + ' UNIQUE '

    + case when (status & 16)=16 then ' CLUSTERED' else ' NONCLUSTERED' end

    from sysindexes

    where name = object_name(@cursorID) and id = @table_ID

    declare @thiskey nvarchar(131), -- 128+3

    @keys nvarchar(2126) -- a specific size for MS for whatever reason

    select @keys = index_col(@table_name, @indid, 1), @i = 2

    if (indexkey_property(@table_id, @indid, 1, 'isdescending') = 1)

    select @keys = @keys + '(-)'

    select @thiskey = index_col(@table_name, @indid, @i)

    if ((@thiskey is not null) and (indexkey_property(@table_ID, @indid, @i, 'isdescending') = 1))

    select @thiskey = @thiskey + '(-)'

    while (@thiskey is not null)

    begin

    select @keys = @keys + ', ' + @thiskey, @i = @i + 1

    select @thiskey = index_col(@table_name, @indid, @i)

    if ((@thiskey is not null) and (indexkey_property(@table_ID, @indid, @i, 'isdescending') = 1))

    select @thiskey = @thiskey + '(-)'

    end

    Select @CreateStatement=@CreateStatement + '(' + @keys + '),'

    fetch next from c1 into @cursorID

    end

    close c1

    deallocate c1

    --CODE TO ADD CHECK CONSTRAINTS TO THE BOTTOM OF THE DEFINITION?

    --CODE TO ADD INDEXES TO THE BOTTOM OF THE DEFINITION?

    --at this point, there is a trailing comma, or it blank

    DECLARE @ptrval binary(16),@txtlen INT

    if len(@CreateStatement) > 0

    BEGIN

    SELECT @ptrval = TEXTPTR(info) ,

    @txtlen = DATALENGTH(info)

    FROM #CreateStatements

    WHERE uid=@uid

    UPDATETEXT #CreateStatements.info @ptrval @txtlen 0 @CreateStatement

    END

    --note that this trims the trailing comma from the end of the statement

    SELECT @ptrval = TEXTPTR(info) ,

    @txtlen = DATALENGTH(info) - 1

    FROM #CreateStatements

    WHERE uid=@uid

    SELECT @CreateStatement= ')'+ CHAR(13)

    UPDATETEXT #CreateStatements.info @ptrval @txtlen 1 @CreateStatement

    Select info from #CreateStatements

    drop table #CreateStatements

    end

  • Lowell

    SSC Guru

    Points: 323444

    leaders_j that code snippet you have is really old; i bet it's from 5 years ago.

    It's from a project i play with now and then that scripts any table via TSQL.

    are you trying to script the CREATE table statements?

    i've got some really advanced versions of that code since i wrote that, that are no longer cursor based.

    try this version for now:

    Get any Tables DDL via TSQL

    my newest version (v309) scripts any object act all(procedure/function/table/view etc.

    I should note that everything my script does, the GUI does, and probably better, but i love writing my own solutions.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • leaders_j

    SSC-Addicted

    Points: 485

    Thanks for the code anyway, But I still need a favour.

    The SQL code you gave me has the following errors in Sql Server 2000. They include

    Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 40

    Line 40: Incorrect syntax near 'max'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 55

    Must declare the variable '@vbCrLf'.

    Server: Msg 195, Level 15, State 1, Procedure sp_GetDDL, Line 63

    'schema_id' is not a recognized function name.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 71

    Must declare the variable '@FINALSQL'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 71

    Must declare the variable '@FINALSQL'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 79

    Must declare the variable '@FINALSQL'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 89

    Must declare the variable '@FINALSQL'.

    Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 312

    Line 312: Incorrect syntax near 'max'.

    Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 336

    Line 336: Incorrect syntax near 'APPLY'.

    Server: Msg 156, Level 15, State 1, Procedure sp_GetDDL, Line 353

    Incorrect syntax near the keyword 'FOR'.

    Server: Msg 156, Level 15, State 1, Procedure sp_GetDDL, Line 364

    Incorrect syntax near the keyword 'ORDER'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 385

    Must declare the variable '@CONSTRAINTSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 390

    Must declare the variable '@INDEXSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 390

    Must declare the variable '@CONSTRAINTSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 434

    Must declare the variable '@INDEXSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 461

    Must declare the variable '@INDEXSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 462

    Must declare the variable '@vbCrLf'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 467

    Must declare the variable '@CHECKCONSTSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 468

    Must declare the variable '@CHECKCONSTSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 481

    Must declare the variable '@FKSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 483

    Must declare the variable '@FKSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 496

    Must declare the variable '@RULESCONSTSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 497

    Must declare the variable '@RULESCONSTSQLS'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 514

    Must declare the variable '@TRIGGERSTATEMENT'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 515

    Must declare the variable '@TRIGGERSTATEMENT'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 522

    Must declare the variable '@TRIGGERSTATEMENT'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 523

    Must declare the variable '@vbCrLf'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 528

    Must declare the variable '@EXTENDEDPROPERTIES'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 529

    Must declare the variable '@EXTENDEDPROPERTIES'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 536

    Must declare the variable '@EXTENDEDPROPERTIES'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 537

    Must declare the variable '@vbCrLf'.

    Server: Msg 137, Level 15, State 2, Procedure sp_GetDDL, Line 543

    Must declare the variable '@FINALSQL'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 548

    Must declare the variable '@FINALSQL'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 549

    Must declare the variable '@FINALSQL'.

    Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 552

    Must declare the variable '@FINALSQL'.

  • Lowell

    SSC Guru

    Points: 323444

    yeah, i switched away from SQL2000 a while ago, and all my scripts are targeted for the newer platform.

    take a look at this script contribution i made for SQL 2000:

    Get DDL for any SQL 2000 table[/url]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • leaders_j

    SSC-Addicted

    Points: 485

    Thanks, the code works but it truncates the result of must of my tables. Is there anything you can do?

  • Lowell

    SSC Guru

    Points: 323444

    leaders_j (10/20/2010)


    Thanks, the code works but it truncates the result of must of my tables. Is there anything you can do?

    since SQL 2000 is limited to varchar(8000), and you may have table definitions that are longer than that, what with the formatting that proc uses, you are limited if you have to have it in a single string. That version will give truncated results for long definitions.

    if a multi row results are ok, you can try this version, which returns a table...basically each column as it's own row:

    Get DDL for Any SQL 2000 Table Version 3

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • leaders_j

    SSC-Addicted

    Points: 485

    I tried the code and I got the following errors

    Server: Msg 8101, Level 16, State 1, Procedure sp_GetDDL2, Line 78

    An explicit value for the identity column in table '#Results' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Server: Msg 8101, Level 16, State 1, Procedure sp_GetDDL2, Line 344

    An explicit value for the identity column in table '#Results' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Server: Msg 8101, Level 16, State 1, Procedure sp_GetDDL2, Line 400

    An explicit value for the identity column in table '#Results' can only be specified when a column list is used and IDENTITY_INSERT is ON.

  • Lowell

    SSC Guru

    Points: 323444

    i don't have a true SQL 2000 installation, and the code works flawlessly in SQL 80 compatibility mode.

    try doing a find and replace for INSERT INTO #Results --> INSERT INTO #Results([sql])

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • leaders_j

    SSC-Addicted

    Points: 485

    Thanks, you've really been helpfull. This time I can not script another table from another database. It give the message -"Table object [dbo].[CASE_CONFIRM] does not exist in Database [master]". I will be grateful if you help me out

  • Lowell

    SSC Guru

    Points: 323444

    it's in the comments of hte proc:

    --#############################################################################

    --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_GetDDL2'

    --#############################################################################

    for SQL 2000, i'm not sure that proc exists...you can do it this way also:

    --Turn system object marking on

    EXEC master.dbo.sp_MS_upd_sysobj_category 1

    GO

    CREATE PROCEDURE sp_GETDDL2 AS ....

    GO

    --Turn system object marking off

    EXEC master.dbo.sp_MS_upd_sysobj_category 2

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply