'Query to return database, schema, table, column for all databases
EDIT: I have edited my original post to add some clarity.
1) Is it possible to write a query that will return database, schema, table, column, and column type, for all databases on the server? In particular, is it possible to join sys.databases with other system catalog views, such as sys.tables? I haven't been able to identify a PK/FK relationship between sys.databases and sys.schema, sys.tables, or other system catalog views.
2) Otherwise, is it possible to write a query that will return the above, where I supply the database name as a parameter, for example using DB_ID('my_database') as a filter for that query? If possible, I would prefer not to use dynamic SQL?
3) Otherwise, if I have to use dynamic SQL, can I load the results of that dynamic SQL into a cursor?
Background: I am writing a stored procedure that generates code for a view. For every character column in the source table (parameter to the SP), I need to call a function that removes garbage characters. I want to create that SP in a utility database, and support creating views across multiple other databases.
Solution 1:[1]
Based on the comments and answers provided, plus my own additional research:
1) No, it is not possible to join sys.databases to other system catalog views, at least the ones I need (sys.schema, sys.tables, sys.columns). Furthermore, from my research, the system catalog views are database specific (for the above views). IOW, sys.tables lists the tables in its database, sys.columns lists columns in its database, etc. This includes the master database.
2) Although @SMor stated it is possible, I have not discovered a way to pass the database name as a parameter, without using dynamic SQL. For example, this does not work:
SELECT * FROM @database.INFORMATION_SCHEMA.COLUMNS
3) However, it is possible to use dynamic SQL + sys.databases to generate the required query as a UNION ALL query. @Zhorov provided a great example. It is also possible to use dynamic SQL to load a cursor. Again, @Zhorov provided a great example, i.e. add the cursor declaration to the dynamic SQL, then loop over the cursor in downstream code. In the future, I may use @Zhorov's code to generate the code to build the below view.
However, after further thought, since I only have to generate views over 6 or so databases, I've decided to "manually" create a view that joins the databases I need to work with:
Version 1 (for my immediate needs):
CREATE VIEW [util].[vwGetColumnInfoISC]
AS
/* Returns column information from selected databases using INFORMATION_SCHEMA views */
WITH cteDeaths
AS (
SELECT *
FROM Deaths.INFORMATION_SCHEMA.COLUMNS
),
cteRLDXDth
AS (
SELECT *
FROM RLDXDth.INFORMATION_SCHEMA.COLUMNS
),
cteRLDXED
AS (
SELECT *
FROM RLDXED.INFORMATION_SCHEMA.COLUMNS
),
cteRLDXHosp
AS (
SELECT *
FROM RLDXHosp.INFORMATION_SCHEMA.COLUMNS
),
cteCaped
AS (
SELECT *
FROM caped.INFORMATION_SCHEMA.COLUMNS
),
cteHierep
AS (
SELECT *
FROM hierep.INFORMATION_SCHEMA.COLUMNS
),
cteUnion
AS (
SELECT *
FROM cteDeaths
UNION ALL
SELECT *
FROM cteRLDXDth
UNION ALL
SELECT *
FROM cteRLDXED
UNION ALL
SELECT *
FROM cteRLDXHosp
UNION ALL
SELECT *
FROM cteCaped
UNION ALL
SELECT *
FROM cteHierep
)
SELECT TOP 999999999999999999 *
FROM cteUnion
ORDER BY
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,ORDINAL_POSITION
GO
Version 2 (for future use, esp. index information - I hope to improve this view in the future to include more information):
CREATE VIEW [util].[vwGetColumnInfoSYS]
AS
/* Returns column information from selected databases using system catalog views */
WITH cteDeaths
AS (
SELECT 'Deaths' AS TABLE_CATALOG
,sch.name AS TABLE_SCHEMA
,tbl.name AS TABLE_NAME
,col.name AS COLUMN_NAME
,col.column_id AS ORDINAL_POSITION
,typ.name AS COLUMN_TYPE
,col.max_length AS MAX_LENGTH
,col.column_id
,col.precision
,col.scale
,col.collation_name
,col.is_nullable
,col.is_rowguidcol
,col.is_identity
,col.is_computed
,idx.name AS index_name
,idx.type AS index_type
,idx.type_desc AS index_description
,idx.is_unique
,idx.data_space_id
,idx.ignore_dup_key
,idx.is_primary_key
,idx.is_unique_constraint
,idx.fill_factor
,idx.is_padded
,idx.is_disabled
,idx.is_hypothetical
,idx.allow_row_locks
,idx.allow_page_locks
,idx.has_filter
,idx.filter_definition
,idx.compression_delay
,ixc.key_ordinal
,ixc.partition_ordinal
,ixc.is_descending_key
,ixc.is_included_column
FROM Deaths.sys.schemas sch
JOIN Deaths.sys.tables tbl
ON sch.schema_id = tbl.schema_id
JOIN Deaths.sys.columns col
ON col.object_id = tbl.object_id
JOIN Deaths.sys.types typ
ON typ.system_type_id = col.system_type_id
LEFT JOIN Deaths.sys.index_columns ixc
ON col.object_id = ixc.object_id
AND col.column_id = ixc.column_id
LEFT JOIN Deaths.sys.indexes idx
ON ixc.object_id = idx.object_id
AND ixc.index_id = idx.index_id
),
cteRLDXDth
AS (
SELECT 'RLDXDth' AS TABLE_CATALOG
,sch.name AS TABLE_SCHEMA
,tbl.name AS TABLE_NAME
,col.name AS COLUMN_NAME
,col.column_id AS ORDINAL_POSITION
,typ.name AS COLUMN_TYPE
,col.max_length AS MAX_LENGTH
,col.column_id
,col.precision
,col.scale
,col.collation_name
,col.is_nullable
,col.is_rowguidcol
,col.is_identity
,col.is_computed
,idx.name AS index_name
,idx.type AS index_type
,idx.type_desc AS index_description
,idx.is_unique
,idx.data_space_id
,idx.ignore_dup_key
,idx.is_primary_key
,idx.is_unique_constraint
,idx.fill_factor
,idx.is_padded
,idx.is_disabled
,idx.is_hypothetical
,idx.allow_row_locks
,idx.allow_page_locks
,idx.has_filter
,idx.filter_definition
,idx.compression_delay
,ixc.key_ordinal
,ixc.partition_ordinal
,ixc.is_descending_key
,ixc.is_included_column
FROM RLDXDth.sys.schemas sch
JOIN RLDXDth.sys.tables tbl
ON sch.schema_id = tbl.schema_id
JOIN RLDXDth.sys.columns col
ON col.object_id = tbl.object_id
JOIN RLDXDth.sys.types typ
ON typ.system_type_id = col.system_type_id
LEFT JOIN RLDXDth.sys.index_columns ixc
ON col.object_id = ixc.object_id
AND col.column_id = ixc.column_id
LEFT JOIN RLDXDth.sys.indexes idx
ON ixc.object_id = idx.object_id
AND ixc.index_id = idx.index_id
),
cteRLDXED
AS (
SELECT 'RLDXED' AS TABLE_CATALOG
,sch.name AS TABLE_SCHEMA
,tbl.name AS TABLE_NAME
,col.name AS COLUMN_NAME
,col.column_id AS ORDINAL_POSITION
,typ.name AS COLUMN_TYPE
,col.max_length AS MAX_LENGTH
,col.column_id
,col.precision
,col.scale
,col.collation_name
,col.is_nullable
,col.is_rowguidcol
,col.is_identity
,col.is_computed
,idx.name AS index_name
,idx.type AS index_type
,idx.type_desc AS index_description
,idx.is_unique
,idx.data_space_id
,idx.ignore_dup_key
,idx.is_primary_key
,idx.is_unique_constraint
,idx.fill_factor
,idx.is_padded
,idx.is_disabled
,idx.is_hypothetical
,idx.allow_row_locks
,idx.allow_page_locks
,idx.has_filter
,idx.filter_definition
,idx.compression_delay
,ixc.key_ordinal
,ixc.partition_ordinal
,ixc.is_descending_key
,ixc.is_included_column
FROM RLDXED.sys.schemas sch
JOIN RLDXED.sys.tables tbl
ON sch.schema_id = tbl.schema_id
JOIN RLDXED.sys.columns col
ON col.object_id = tbl.object_id
JOIN RLDXED.sys.types typ
ON typ.system_type_id = col.system_type_id
LEFT JOIN RLDXED.sys.index_columns ixc
ON col.object_id = ixc.object_id
AND col.column_id = ixc.column_id
LEFT JOIN RLDXED.sys.indexes idx
ON ixc.object_id = idx.object_id
AND ixc.index_id = idx.index_id
),
cteRLDXHosp
AS (
SELECT 'RLDXHosp' AS TABLE_CATALOG
,sch.name AS TABLE_SCHEMA
,tbl.name AS TABLE_NAME
,col.name AS COLUMN_NAME
,col.column_id AS ORDINAL_POSITION
,typ.name AS COLUMN_TYPE
,col.max_length AS MAX_LENGTH
,col.column_id
,col.precision
,col.scale
,col.collation_name
,col.is_nullable
,col.is_rowguidcol
,col.is_identity
,col.is_computed
,idx.name AS index_name
,idx.type AS index_type
,idx.type_desc AS index_description
,idx.is_unique
,idx.data_space_id
,idx.ignore_dup_key
,idx.is_primary_key
,idx.is_unique_constraint
,idx.fill_factor
,idx.is_padded
,idx.is_disabled
,idx.is_hypothetical
,idx.allow_row_locks
,idx.allow_page_locks
,idx.has_filter
,idx.filter_definition
,idx.compression_delay
,ixc.key_ordinal
,ixc.partition_ordinal
,ixc.is_descending_key
,ixc.is_included_column
FROM RLDXHosp.sys.schemas sch
JOIN RLDXHosp.sys.tables tbl
ON sch.schema_id = tbl.schema_id
JOIN RLDXHosp.sys.columns col
ON col.object_id = tbl.object_id
JOIN RLDXHosp.sys.types typ
ON typ.system_type_id = col.system_type_id
LEFT JOIN RLDXHosp.sys.index_columns ixc
ON col.object_id = ixc.object_id
AND col.column_id = ixc.column_id
LEFT JOIN RLDXHosp.sys.indexes idx
ON ixc.object_id = idx.object_id
AND ixc.index_id = idx.index_id
),
cteCaped
AS (
SELECT 'caped' AS TABLE_CATALOG
,sch.name AS TABLE_SCHEMA
,tbl.name AS TABLE_NAME
,col.name AS COLUMN_NAME
,col.column_id AS ORDINAL_POSITION
,typ.name AS COLUMN_TYPE
,col.max_length AS MAX_LENGTH
,col.column_id
,col.precision
,col.scale
,col.collation_name
,col.is_nullable
,col.is_rowguidcol
,col.is_identity
,col.is_computed
,idx.name AS index_name
,idx.type AS index_type
,idx.type_desc AS index_description
,idx.is_unique
,idx.data_space_id
,idx.ignore_dup_key
,idx.is_primary_key
,idx.is_unique_constraint
,idx.fill_factor
,idx.is_padded
,idx.is_disabled
,idx.is_hypothetical
,idx.allow_row_locks
,idx.allow_page_locks
,idx.has_filter
,idx.filter_definition
,idx.compression_delay
,ixc.key_ordinal
,ixc.partition_ordinal
,ixc.is_descending_key
,ixc.is_included_column
FROM caped.sys.schemas sch
JOIN caped.sys.tables tbl
ON sch.schema_id = tbl.schema_id
JOIN caped.sys.columns col
ON col.object_id = tbl.object_id
JOIN caped.sys.types typ
ON typ.system_type_id = col.system_type_id
LEFT JOIN caped.sys.index_columns ixc
ON col.object_id = ixc.object_id
AND col.column_id = ixc.column_id
LEFT JOIN caped.sys.indexes idx
ON ixc.object_id = idx.object_id
AND ixc.index_id = idx.index_id
),
cteHierep
AS (
SELECT 'hierep' AS TABLE_CATALOG
,sch.name AS TABLE_SCHEMA
,tbl.name AS TABLE_NAME
,col.name AS COLUMN_NAME
,col.column_id AS ORDINAL_POSITION
,typ.name AS COLUMN_TYPE
,col.max_length AS MAX_LENGTH
,col.column_id
,col.precision
,col.scale
,col.collation_name
,col.is_nullable
,col.is_rowguidcol
,col.is_identity
,col.is_computed
,idx.name AS index_name
,idx.type AS index_type
,idx.type_desc AS index_description
,idx.is_unique
,idx.data_space_id
,idx.ignore_dup_key
,idx.is_primary_key
,idx.is_unique_constraint
,idx.fill_factor
,idx.is_padded
,idx.is_disabled
,idx.is_hypothetical
,idx.allow_row_locks
,idx.allow_page_locks
,idx.has_filter
,idx.filter_definition
,idx.compression_delay
,ixc.key_ordinal
,ixc.partition_ordinal
,ixc.is_descending_key
,ixc.is_included_column
FROM hierep.sys.schemas sch
JOIN hierep.sys.tables tbl
ON sch.schema_id = tbl.schema_id
JOIN hierep.sys.columns col
ON col.object_id = tbl.object_id
JOIN hierep.sys.types typ
ON typ.system_type_id = col.system_type_id
LEFT JOIN hierep.sys.index_columns ixc
ON col.object_id = ixc.object_id
AND col.column_id = ixc.column_id
LEFT JOIN hierep.sys.indexes idx
ON ixc.object_id = idx.object_id
AND ixc.index_id = idx.index_id
),
cteUnion
AS (
SELECT *
FROM cteDeaths
UNION ALL
SELECT *
FROM cteRLDXDth
UNION ALL
SELECT *
FROM cteRLDXED
UNION ALL
SELECT *
FROM cteRLDXHosp
UNION ALL
SELECT *
FROM cteCaped
UNION ALL
SELECT *
FROM cteHierep
)
SELECT TOP 999999999999999999 *
FROM cteUnion
ORDER BY
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,ORDINAL_POSITION
GO
I do wish SQL Server provided such an overarching view out-of-the-box, which was the gist of my original question(s).
Finally, I wrote a code generator to generate the views I require:
(See http://www.sqlservercentral.com/scripts/T-SQL/117890/ for PatExclude8K):
CREATE PROCEDURE [dbo].[spGenerateCleanViews2]
@srcTableName SYSNAME -- Must be a 3-level table name, case-insensitive
,@tgtTableName SYSNAME = 'cln.vw{srcTable}'
,@debug BIT = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Declare variables
DECLARE @srcDatabase SYSNAME
,@srcSchema SYSNAME
,@srcTable SYSNAME
,@tgtDatabase SYSNAME
,@tgtSchema SYSNAME
,@tgtTable SYSNAME
;
DECLARE @columnName SYSNAME
,@columnType SYSNAME
,@sql NVARCHAR(MAX) = ''
,@DBExec NVARCHAR(100)
,@line NVARCHAR(256)
,@n INT
,@pattern VARCHAR(100) = '%[^ -~]%' -- Low order ASCII print characters CHAR(32) - CHAR(126)
;
-- Parse source table
SET @srcTable = PARSENAME(@srcTableName,1);
SET @srcSchema = PARSENAME(@srcTableName,2);
SET @srcDatabase = PARSENAME(@srcTableName,3);
-- Don't try to set defaults - if it's not a three level name then abort
IF @srcTable IS NULL
OR @srcSchema IS NULL
OR @srcDatabase IS NULL
BEGIN
RAISERROR ('A three-level table name (server.schema.table) is required.',16,1);
RETURN;
END
-- Parse target table
SET @tgtTable = PARSENAME(@tgtTableName,1);
SET @tgtSchema = PARSENAME(@tgtTableName,2);
SET @tgtDatabase = PARSENAME(@tgtTableName,3);
-- Set defaults if NULL
IF @tgtDatabase IS NULL
SET @tgtDatabase = DB_NAME()
IF @tgtSchema IS NULL
SET @tgtSchema = 'cln'
IF @tgtTable IS NULL
SET @tgtTable = 'vw{srcTable}'
-- Replace tokens in the target table name
SET @tgtTable = REPLACE(@tgtTable,'{srcTable}',@srcTable);
-- Create scrollable cursor
BEGIN TRY
DECLARE cursorColumns SCROLL CURSOR FOR SELECT COLUMN_NAME
,DATA_TYPE
,ORDINAL_POSITION
FROM util.vwGetColumnInfoISC
WHERE TABLE_CATALOG = @srcDatabase COLLATE Latin1_General_100_CI_AI
AND TABLE_SCHEMA = @srcSchema COLLATE Latin1_General_100_CI_AI
AND TABLE_NAME = @srcTable COLLATE Latin1_General_100_CI_AI
ORDER BY
ORDINAL_POSITION
END TRY
BEGIN CATCH
DEALLOCATE cursorColumns;
DECLARE cursorColumns SCROLL CURSOR FOR SELECT COLUMN_NAME
,DATA_TYPE
,ORDINAL_POSITION
FROM util.vwGetColumnInfoISC
WHERE TABLE_CATALOG = @srcDatabase COLLATE Latin1_General_100_CI_AI
AND TABLE_SCHEMA = @srcSchema COLLATE Latin1_General_100_CI_AI
AND TABLE_NAME = @srcTable COLLATE Latin1_General_100_CI_AI
ORDER BY
ORDINAL_POSITION
END CATCH
OPEN cursorColumns;
FETCH FIRST FROM cursorColumns INTO @columnName,@columnType,@n;
WHILE @@fetch_status = 0
BEGIN
SET @line = REPLICATE(' ',256);
IF @columnType LIKE '%char'
BEGIN
SET @line = STUFF(@line,7,50,',LTRIM(RTRIM([t{alias}].[NewString]))'); -- strip leading and trailing spaces
SET @line = REPLACE(@line,'{alias}',FORMAT(@n,'00'));
SET @line = STUFF(@line,100,50,'AS {columnName}');
SET @line = REPLACE(@line,'{columnName}',QUOTENAME(@columnName,'['))
END
ELSE
BEGIN
SET @line = STUFF(@line,7,50,',[src].{columnName}');
SET @line = REPLACE(@line,'{columnName}',QUOTENAME(@columnName,'['))
SET @line = STUFF(@line,100,50,'AS {columnName}');
SET @line = REPLACE(@line,'{columnName}',QUOTENAME(@columnName,'['))
END
SET @sql += RTRIM(@line) + CHAR(13);
FETCH NEXT FROM cursorColumns INTO @columnName,@columnType,@n;
END
-- Source Table
-- Note: If the source table is in a different database than the target database then a synonym must be created!
SET @line = REPLICATE(' ',256);
SET @line = STUFF(@line,3,50,'FROM {srcSchema}.{srcTable} src');
SET @sql += RTRIM(@line) + CHAR(13);
-- Cross Apply
FETCH FIRST FROM cursorColumns INTO @columnName,@columnType,@n;
WHILE @@fetch_status = 0
BEGIN
SET @line = REPLICATE(' ',256);
IF @columnType LIKE '%char'
BEGIN
SET @line = STUFF(@line,3,60,'CROSS APPLY dbo.fnPatExclude8K_Table([src].{columnName},''{pattern}'') t{alias}');
SET @line = REPLACE(REPLACE(REPLACE(@line,'{columnName}',QUOTENAME(@columnName,'[')),'{alias}',FORMAT(@n,'00')),'{pattern}',@pattern);
SET @sql += RTRIM(@line) + CHAR(13);
END
FETCH NEXT FROM cursorColumns INTO @columnName,@columnType,@n;
END
CLOSE cursorColumns;
DEALLOCATE cursorColumns;
SET @sql = STUFF(@sql,1,7,'SELECT ');
-- Do not indent the following code block
SET @sql =
'CREATE OR ALTER VIEW {tgtSchema}.{tgtTable}
AS
' + @sql
;
SET @sql = REPLACE(@sql,'{srcSchema}',QUOTENAME(@srcSchema,'['));
SET @sql = REPLACE(@sql,'{srcTable}',QUOTENAME(@srcTable,'['));
SET @sql = REPLACE(@sql,'{tgtDatabase}',QUOTENAME(@tgtDatabase,'['));
SET @sql = REPLACE(@sql,'{tgtSchema}',QUOTENAME(@tgtSchema,'['));
SET @sql = REPLACE(@sql,'{tgtTable}',QUOTENAME(@tgtTable,'['));
-- If debugging display generated code
-- XML is used to overcome the 4000 character limit of PRINT
IF @debug = 1
SELECT CAST('<![CDATA[' + @sql + ']]>' AS XML);
ELSE
BEGIN
-- Set execution context
SET @DBExec = @tgtDatabase + N'.sys.sp_executesql';
EXEC @DBExec @sql;
END
END
GO
Solution 2:[2]
- For all databases: select * from sys.databases
- For all tables: Select * from INFORMATION_SCHEMA.TABLES Where TABLE_TYPE ='BASE TABLE'
- From all Views Select * from INFORMATION_SCHEMA.TABLES Where TABLE_TYPE ='VIEW'
- Fro all columns: Select * from INFORMATION_SCHEMA.COLUMNS please use table_name as filter.
- In the INFORMATION_SCHEMA.COLUMNS table you will get the DATA_TYPE for column type
First load the databases from the SQL server database, then get the database names and connect to that database, after that use all queries above to get what you need.
Solution 3:[3]
I have created a stored procedure that can be return the column info if you pass the database name.
Create PROCEDURE SP_GetColumnInfo
@DatabaseName varchar(max)
AS
BEGIN
SET NOCOUNT ON;
EXEC('USE ' + @DatabaseName + '; Select DatabaseName = '''+ @DatabaseName +''', Table_Schema, Table_Name, Column_Name, Data_Type from INFORMATION_SCHEMA.COLUMNS as ColumnNames');
END
GO
Solution 4:[4]
This is an answer to your third question and next approach generates one dynamic T-SQL statement, that returns your expected result. You may try to execute it directly or create a cursor.
-- Declarations
DECLARE @stm nvarchar(max)
SET @stm = N''
-- Dynamic SQL
SELECT @stm = (
SELECT CONCAT(
N'UNION ALL ',
N'SELECT N''',
name,
N''' AS DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE '+
N'FROM ',
QUOTENAME([name]),
N'.INFORMATION_SCHEMA.COLUMNS '
)
FROM sys.databases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb')
FOR XML PATH('')
)
SET @stm = STUFF(@stm, 1, 10, N'')
PRINT @stm
-- Execution without cursor
EXEC sp_executesql @stm
-- Execution with cursor
SET @stm = CONCAT(
N'DECLARE info CURSOR FORWARD_ONLY READ_ONLY FOR ',
@stm
)
EXEC sp_executesql @stm
OPEN info
-- Fetch operations
CLOSE info
DEALLOCATE info
Solution 5:[5]
I was having a similar issue to you. Here is what I ended up with, though in a perfect world I'd like to add row_count per table and column data_type. The answer to adding db to schema, table, column info is the function DB_NAME()
select
s.name schema_name_,
t.name table_name,
c.name col_name_,
idx.name index_type,
DB_NAME()
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
left join sys.index_columns ic on c.object_id = ic.object_id and c.column_id = ic.column_id
left join sys.indexes idx on ic.index_id = idx.index_id and ic.object_id = idx.object_id
;
Solution 6:[6]
The question as asked makes no reference to any prior efforts, no sample sql that did not work. What is missing is any sharing of that effort, or describing what is the problem or what error messages are being received.
Take a look at the sixth (or so) item returned from the google
sqlserver get database, schema, table, column, column type for all databases
Look at the section sp_msforeachtable where they cite function EXEC sp_MSforeachdb '...'
and the section Exploring your Data Model in Depth, with Listing 23: Exploring columns and their data types.
Tailor these resources to your question as asked.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Scott |
| Solution 2 | Hasan Mahmood |
| Solution 3 | Prahalad Gaggar |
| Solution 4 | |
| Solution 5 | Dale K |
| Solution 6 | donPablo |