Post reply

System Tables - Datattypes - full declaration

  • October 17, 2002 at 10:07 am

    #80242

    Related to another question on how to decipher system tables, I have a need in a script to generate the declaration for a column's datatype.

    That's pretty straightforward for "int" and not too hard for even Decimal, but how does one do this in general, to make sure user defined datatypes and all the variations are included.

    Is there a way from T-SQL to generate a script for a table?

    Here is the code I am using, it's a bit too simplistic and would appreciate a pointer to either info on the right way, or just some code. I've looked at various scripts floating around and most tend to generate human-readable information. What I want is the actual SQL to re-declare the variable (I'm trying to generate shadow auditing tables that look the same as the primary table, and do it in T-SQL).

    declare LoopAllCols cursor scroll for

    select c.name, s1.name as Datatype, c.length, c.prec, c.scale

    from syscolumns c

    left join systypes s1 on s1.usertype=c.usertype

    where c.id=Object_Id(@Table)

    open LoopAllCols

    fetch first from LoopAllCols into @Colname, @ColType, @ColMaxLen, @ColPrec, @ColScale

    while @@Fetch_Status = 0

    begin

    set @fulltype = @Coltype

    if @ColType='Varchar' set @fulltype = @fulltype + '(' + convert(varchar(5),@ColMaxLen) + ')'

    if @ColType='Char' set @fulltype = @fulltype + '(' + convert(varchar(5),@ColMaxLen) + ')'

    if @ColType='Decimal' set @fulltype = @fulltype + '(' + convert(varchar(5),@ColPrec) + ', ' + convert(char(5),@ColScale) + ')'

    set @outstr = @Outstr + ' Old_' + @Colname + ' ' + @FullType

    fetch next from LoopAllCols into @Colname, @ColType, @ColMaxLen, @ColPrec, @ColScale

    if @@fetch_status = 0

    set @outstr = @Outstr + ', '

    else

    set @outstr = @outstr + ') ' + char(13) + char(10)

    set @outstr = @outstr + char(13) + char(10)

    end

    Edited by - Ferguson on 10/17/2002 10:13:48 AM

  • October 17, 2002 at 1:16 pm

    #438813

    I recently submitted a stored procedure that will script tables. If you go to the script section here and look at the most recent ones you will find one titled:

    Create And Execute Table Script (SQL Server 2000)

    This SP will show you how to gather all the info you need to create a script for a table.

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • October 17, 2002 at 1:52 pm

    #438818

    Thanks for the pointer. You had adopted a similar approach (though much more complete) where you explicitly look for certain datattypes to decide which have one parameter (e.g. char) or two (e.g. decimal). I was hoping for magic somewhere, some call that did all that for you.

    But thanks, very handy routine.

  • November 17, 2002 at 8:26 pm

    #441582

    I ran across an undocumented answer to my question, here's an example:

    declare @Tabid int

    declare @STR nvarchar(255)

    set @Tabid = Object_id('Customer')

    exec sp_gettypestring @tabId, 6, @STR output

    select @STR

    This apperas to reurn the exact declaration.

Viewing 4 posts - 1 through 3 (of 3 total)

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