I wanted to get column name of a table, for that I've used below query, which works fine.
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'G001'
But now that I want to get column name of table from specific database, for the same I'm using below query ,but it doesn't giving any output.
In e.g. HS is my database name out of multiple databases and G001 is my table name.
And in my case I need to pass database name and table name as parameter.
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'HS..G001'
So my question is how do I get column name of table from specific database?
You use the INFORMATION_SCHEMA in the database you want to query, eg:
SELECT COLUMN_NAME
FROM HS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='G001'
If you need to pass this as a parameter, then you need to write some dynamic SQL:
DECLARE @database VARCHAR(255) = 'HS'
DECLARE @table VARCHAR(255) = 'G001'
DECLARE @sql VARCHAR(1000) = 'SELECT COLUMN_NAME
FROM ' + @database + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''' + @table + ''''
EXEC(@sql)
Note: Dynamic SQL like this can be vulnerable to SQL injection so make sure you sanitise your inputs.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments