Get all columns of a table from a different database

Question

s there a syntax wherein I can get the columns of a table from a different database? I have tried using select column_name from information_schema.columns where table_name = 'Database_Name.DBO.Table_Name' But it's not working. Please do help me, been doing this for days.

Answer 1

You're close. TABLE_NAME has just the name of the table. The rest of the information is in TABLE_CATALOG and TABLE_SCHEMA. You can qualify information_schema with the catalog you want to select from:

select column_name from Database_Name.information_schema.columns
where table_name = 'Table_Name' AND table_schema = 'dbo'

Answer 2

This is what I found out. Each database has its own information_schema view so it won't show the information from the other databases. So if you want to get the column names for a table in another database, you have to use the information_schema view of that database.

{database name}.information_schema

Example:

SET @Sql = CONCAT('INSERT INTO ', @DBName, '.dbo.colname_table (column_name) 
    SELECT COLUMN_NAME FROM ', @DBName, '.INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = ''table_name'' AND TABLE_SCHEMA = ''dbo'' 
    ORDER BY ORDINAL_POSITION');

EXECUTE sp_executesql @Sql;

where @DBName is the name of the database.