Get Default constraints for all tables across Databases

Get Default constraints for all tables across Databases

 

CREATE TABLE #dbTmp (id int identity(1,1), dbName nvarchar(200))

CREATE TABLE #dbName (id int identity(1,1), dbName nvarchar(200), ColName nvarchar(200), ConstName nvarchar(200), TableName nvarchar(200))

DECLARE @cnt int = 0

DECLARE @dbTmp varchar(200)

DECLARE @tblTmp varchar(200)

DECLARE @var varchar(1000)

INSERT INTO #dbTmp

SELECT name as dbName from sys.databases where name NOT in ('model', 'master', 'msdb', 'tempdb', 'ReportServer','ReportServerTempDB')

SELECT @cnt = @@Rowcount

WHILE @cnt <> 0

BEGIN

SELECT @dbTmp = dbName from #dbTmp where id = @cnt

SELECT @var = ' USE ' + @dbTmp

PRINT @var

EXECUTE (@var)

SELECT @var = 'INSERT INTO tmpdb..#dbName (dbName, ColName,ConstName, TableName) '

SELECT @var = @var + 'SELECT ''' + @dbTmp +''', c.name as colName , sc.name as ConstName ,t.name as TableName FROM '

+@dbTmp+'.sys.default_constraints sc INNER JOIN '

+@dbTmp+'.sys.tables t on sc.parent_object_id = t.object_id INNER JOIN '

+@dbTmp+'.sys.columns c on t.object_id = c.object_id and sc.parent_column_id = c.column_id '

PRINT @var

EXECUTE (@var)

SELECT @cnt = @cnt - 1

END

SELECT * FROM #dbName

DROP TABLE #dbTmp

DROP TABLE #dbName