Get the schema of tables and load it to a table

[nivedhitha]

Hi everyone,

I have a situation where I have to get a set of tables from SQL Server and get the schema(columns, datatypes, schema etc) details of all the tables and load these into a table.

Any suggestions/ idea?

Thanks in advance

[Anonymous · Accepted solution]

you can use tMSSqlInput to execute below SQL. It will give you the complete list of schema. you can dump it to a file and load it to the target.

select COLUMN_NAME, DATA_TYPE,IS_NULLABLE,
       CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)
          WHEN NUMERIC_PRECISION is not NULL then
              CASE WHEN NUMERIC_SCALE IS NOT NULL THEN '('+ CONVERT(VARCHAR,NUMERIC_PRECISION)+','+CONVERT(VARCHAR,NUMERIC_SCALE)+')'
               ELSE '('+ CONVERT(VARCHAR,NUMERIC_PRECISION)+', 0)'
                END
       END as LENGTH
from INFORMATION_SCHEMA.COLUMNS
where  TABLE_SCHEMA='<yourSchema>';

Hope this helps.

Regards,

Ragu