|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DECLARE and table strucutresWe knew that we could retrieve the structure of a table via
INFORMATION_SCHEMA.COLUMNS. Is there a way to convert the structure into a DECLARE variables statement? For example: From this: Table_A ( f1 char(10), f2 integer ) To this: Declare @f1 char(10), @f2 integer This way, the declared variables' datatype would always match with the table. -- Man-wai Chang Softmedia Technology Co., Ltd. Tel: (852)3583 2780 Hi,
Take a look into user defined data types in books online.. Thanks Hari SQL Server MVP Show quoteHide quote "Man-wai Chang" <i***@softmedia.hk> wrote in message news:ONEAQ8u0GHA.4044@TK2MSFTNGP04.phx.gbl... > > We knew that we could retrieve the structure of a table via > INFORMATION_SCHEMA.COLUMNS. > > Is there a way to convert the structure into a DECLARE variables > statement? For example: > > From this: > Table_A ( f1 char(10), f2 integer ) > To this: > Declare @f1 char(10), @f2 integer > > This way, the declared variables' datatype would always match with the > table. > > -- > Man-wai Chang > Softmedia Technology Co., Ltd. > Tel: (852)3583 2780 INFORMATION_SCHEMA.COLUMNS also contains DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, and NUMERIC_SCALE. Seems like everything you need to build the Declare statements is available. -- Show quoteHide quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Man-wai Chang" <i***@softmedia.hk> wrote in message news:ONEAQ8u0GHA.4044@TK2MSFTNGP04.phx.gbl... > > We knew that we could retrieve the structure of a table via > INFORMATION_SCHEMA.COLUMNS. > > Is there a way to convert the structure into a DECLARE variables > statement? For example: > > From this: > Table_A ( f1 char(10), f2 integer ) > To this: > Declare @f1 char(10), @f2 integer > > This way, the declared variables' datatype would always match with the > table. > > -- > Man-wai Chang > Softmedia Technology Co., Ltd. > Tel: (852)3583 2780 Arnie Rowland wrote:
> INFORMATION_SCHEMA.COLUMNS also contains DATA_TYPE, Yes, but how could I do it? Via EXECSQL()? Or is there a smarter way?> CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, and NUMERIC_SCALE. > > Seems like everything you need to build the Declare statements is available. > declare thesql varchar(1024) declare colname varchar(100), @dtype varchar(20), @mxlen integer declare tabstruct cursor for select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMAITON_SCHEMA.COLUMNS open tabstruct fetch next from tabstruct into @colname, @dtype, @mxlen while @@fetch_status=0 begin set @thesql=@column_name+' '+@dtype+'('+@mxlen+')' execsql(@thesql) fetch next from tabstruct into @colname, @dtype, @mxlen end close tabstruct deallocate tabstruct -- Man-wai Chang Softmedia Technology Co., Ltd. Tel: (852)3583 2780 Hi,
Initially i understood your question wrongly. My mistake. You have to use dynamic SQL execution using EXEC or SP_ExecuteSQL Thanks Hari SQL Server MVP Show quoteHide quote "Man-wai Chang" <i***@softmedia.hk> wrote in message news:uCVUOBx0GHA.4228@TK2MSFTNGP06.phx.gbl... > Arnie Rowland wrote: >> INFORMATION_SCHEMA.COLUMNS also contains DATA_TYPE, >> CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, and NUMERIC_SCALE. >> >> Seems like everything you need to build the Declare statements is >> available. >> > > Yes, but how could I do it? Via EXECSQL()? Or is there a smarter way? > > declare thesql varchar(1024) > declare colname varchar(100), @dtype varchar(20), @mxlen integer > > declare tabstruct cursor for select COLUMN_NAME, DATA_TYPE, > CHARACTER_MAXIMUM_LENGTH > from INFORMAITrON_SCHEMA.COLUMNS > open tabstruct > fetch next from tabstruct into @colname, @dtype, @mxlen > while @@fetch_status=0 begin > set @thesql=@column_name+' '+@dtype+'('+@mxlen+')' > execsql(@thesql) > fetch next from tabstruct into @colname, @dtype, @mxlen > end > close tabstruct > deallocate tabstruct > > -- > Man-wai Chang > Softmedia Technology Co., Ltd. > Tel: (852)3583 2780
Other interesting topics
UDF that take a UDT as a parameter (CLR)
Table reaching max value of identity very early Crosstab, Pivot? Visual Basic 6.0 vs .NET Framework TOP 10 for each group Computing Medians The Joe Celko Way Paging with ROW_NUMBER on filtered records (SQL SERVER 2005) inner join in an update FOR XML AUTO, ELEMENTS Problem xp_smtp_sendmail send out corrupted email message for large attach |
|||||||||||||||||||||||