CODEWITHSUNDEEP
sqlsql-serverdatabasessms
disasterkid
disasterkid

Reputation: 7278

Get Columns of another table located in another database on the current server

My current selected database is DATABASE_A. I would like to write a SELECT query that returns all the column names of TABLE_NAME that are located on another database called DATABASE_B. Both databases are located on the same server. Is that possible?

Update: I think I would have improve my question: What I have in hand is not two separate variables for the name of the database and the name of the table. That means all I have is DATABASE_B.TABLE_NAME. In the the current answers (which are actually very correct), we are able to separate database name from table name.

Upvotes: 0

Views: 3349

Answers (2)

macoms01
macoms01

Reputation: 1110

Here is my updated procedure:

declare @dbName varchar(100),
        @tableName varchar(100)

declare @stringToParse varchar(1000) = 'DatabaseName.TableName'

set     @dbName = SUBSTRING(@stringToParse, 0, charindex('.', @stringToParse))
set     @tableName = SUBSTRING(@stringToParse, charindex('.', @stringToParse) + 1, len(@stringToParse) - charindex('.', @stringToParse))

select  @dbName, @tableName



declare @stringToExecute varchar(max) = 'select  c.name
from    ' + @dbName + '.sys.tables t inner join
        ' + @dbName + '.sys.columns c on t.object_id = c.object_id
where   t.name = ''' +  @tableName + ''''

select  @stringToExecute -- this will show you what was generated
exec(@stringToExecute) -- this will run the sql that was generated.

Here is the output from select @stringToExecute

select  c.name
from    DatabaseName.sys.tables t inner join
        databaseName.sys.columns c on t.object_id = c.object_id
where   t.name = 'TableName'

I'm not sure where the database and table names come from, but generating this dynamic SQL is extremely dangerous! While this code will get you what you want, you may want to re-think it before putting it to use.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93754

use Information_Schema.Columns with Database name

USE DATABASE_A

SELECT *
FROM   DATABASE_B.INFORMATION_SCHEMA.columns
WHERE  TABLE_NAME = 'TABLE_NAME'

Upvotes: 4

Related Questions