Search For Column Name in All Databases
Whipped up this little script today to search for a column in all my databases.
-------------------------------------------------------------------------------------------------- -- Search for the column name -------------------------------------------------------------------------------------------------- DECLARE @searchFor AS VARCHAR(50) = 'CustomerID' -- keyword to search for DECLARE @databaseList AS VARCHAR(MAX) = 'Database1,Database2,Database3,Database4' -- comma delimited DECLARE @orderBy AS VARCHAR(50) = 'DateCreated ASC' -- You can also append a space and the ASC/DESC -- Order By One of These -- ObjectID, ColumnName, Table, Database, Date Created, Modified Date -------------------------------------------------------------------------------------------------- DECLARE @sql NVARCHAR(MAX) = '' -- loop SET @databaseList = @databaseList + ',' DECLARE @pos AS INT DECLARE @val AS VARCHAR(255) WHILE PATINDEX('%,%', @databaseList) 0 BEGIN SELECT @pos = PATINDEX('%,%', @databaseList) SELECT @val = LEFT(@databaseList, @pos - 1) SELECT @databaseList = STUFF(@databaseList, 1, @pos, NULL) SET @sql = @sql + 'SELECT * FROM ( SELECT portalColumns.OBJECT_ID AS ObjectID , portalColumns.[NAME] ColumnName , ''[' + @val + '].dbo.['' + portalTables.[NAME] + '']'' AS [Table] , ''[' + @val + ']'' AS ''DatabaseName'', create_date AS ''DateCreated'' , modify_date AS ''ModifiedDate'' FROM [' + @val + '].sys.columns portalColumns INNER JOIN [' + @val + '].sys.tables portalTables ON portalColumns.object_id = portalTables.object_id WHERE portalColumns.name LIKE ''%' + @searchFor + '%'' ) AS Results UNION ' END SET @sql = LEFT(@sql, LEN(@sql) - 5) + ' ORDER BY ' + @orderBy --PRINT @sql EXECUTE sp_executesql @sql
Categories