Reputation: 21
I have the code to get the column names from a SQL Server table, but I want to pass the database name and schema and table name to get the columns.
I tried using information_schema
and many other ways like using system base tables but none is close to what I am trying.
I am checking for this:
select column_name
from mydatabase.schema.tablename
Upvotes: 0
Views: 896
Reputation: 95534
All the information you want is stored in the sys
objects, and some in the INFORMATION_SCHEMA
objects (the latter are more for compatibility as they do not cover all of the information of objects in SQL Server). Apparently, as well, the INFORMATION_SCHEMA
objects can report the incorrect value for the object's schema, however, that has been a point myself and several others have contested with the documentation authors for some time.
Anyway, if we assume we're still using INFORMATION_SCHEMA
parametrising the table and schema is simple:
DECLARE @schema sysname,
@table sysname;
SET @schema = N'dbo';
SET @table = N'mytable';
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table;'
Parametrising the database is a little different. The data in the INFORMATION_SCHEMA
(and sys
) objects is relevant to the database you are currently connected to. You can use 3 part naming, for example mydatabase.INFORMATION_SCHEMA.COLUMNS
, however, you cannot parametrise an objects name. Something like @database.INFORMATION_SCHEMA.COLUMNS
won't work.
You therefore need to use a dynamic statement here:
DECLARE @database sysname,
@schema sysname,
@table sysname;
SET @database = N'mydatabase';
SET @schema = N'dbo';
SET @table = N'mytable';
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT *
FROM ' + QUOTENAME(@database) + N'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table;';
EXEC sys.sp_executesql @SQL, N'@schema sysname, @table sysname', @schema, @table;
As noted, the INFORMATION_SCHEMA
objects aren't complete for SQL Server, and they do have the schema caveat. Personally I prefer using the sys
objects. For a non-dynamic query that would be the following to get the table's column names:
SELECT c.[name] AS ColumnName
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
WHERE s.[name] = @schema
AND t.[name] = @table;
Upvotes: 1