I'm trying to iterate through all databases by listing all table constraints. I've achieved the following:
DECLARE @sql nvarchar(max) = ''
SET @sql =
'
USE ?
select con.[name] as constraint_name,
schema_name(t.schema_id) + ''.'' + t.[name] as [table],
col.[name] as column_name,
con.[definition],
case when con.is_disabled = 0
then ''Active''
else ''Disabled''
end as [status]
from sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
order by con.name
'
PRINT @sql
EXEC sp_MSforeachdb @sql
I would like to have the query UNION all of my results in one clean select.
Can anyone help?
Using SQL Server 2016.
One way is to insert the intermediate results into a temp table. Below is an example that also includes the database name in the results.
CREATE TABLE #results (
[database_name] sysname
,[constraint_name] nvarchar(128)
,[table] nvarchar(257)
,[column_name] nvarchar(128)
,[definition] nvarchar(max)
,[status] varchar(8)
);
DECLARE @sql nvarchar(max) = ''
SET @sql =
N'
USE [?];
INSERT INTO #results
select
N''?'' as database_name,
con.[name] as constraint_name,
schema_name(t.schema_id) + ''.'' + t.[name] as [table],
col.[name] as column_name,
con.[definition],
case when con.is_disabled = 0
then ''Active''
else ''Disabled''
end as [status]
from sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id;
';
PRINT @sql;
EXEC sp_MSforeachdb @sql;
SELECT * FROM #results;