DECLARE and table strucutres

8 Sep 2006 2:32 AM

Man-wai Chang

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

8 Sep 2006 2:51 AM

Hari Prasad

Hi,

Take a look into user defined data types in books online..

Thanks
Hari
SQL Server MVP

Show 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

8 Sep 2006 4:52 AM

Arnie Rowland

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.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show 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

8 Sep 2006 6:30 AM

Man-wai Chang

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

8 Sep 2006 12:21 PM

Hari Prasad

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

9 Sep 2006 3:37 AM

Man-wai Chang

> Initially i understood your question wrongly. My mistake.

My mother tongue is not English. SOrry.

> You have to use dynamic SQL execution using EXEC or SP_ExecuteSQL

Thanks

--
Man-wai Chang
Softmedia Technology Co., Ltd.
Tel: (852)3583 2780