System Tables - Datattypes - full declaration

Question

System Tables - Datattypes - full declaration

Ferguson

SSCertifiable

Points: 5837
More actions
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

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

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

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

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

Ferguson SSCertifiable Points: 5837 More actions · Answer 2

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.

Ferguson SSCertifiable Points: 5837 More actions · Answer 3

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.