/******************************************************************* * Project: I-Tech SQL Scripts * Keywords: [Administration] * Name: SEARCH.AllTables.AllDatabase.ColumnsOfType.sql * Version: 1.0 * Editions: 2005, 2008, 2008 R2 * Prereqs: * Copyright (C) 2012 I-Tech Solutions, Inc. under Microsoft Reciprocal License (MS-RL) http://www.microsoft.com/en-us/openness/licenses.aspx * * Author: Date: Version: Summary: * Ben Johnston 2012-10-22 1.0 Created * * Purpose: Find all columns of the specified type in a server. * * Notes: Set the variable @strTypeToFind to the * data type you are trying to find. * ********************************************************************/ SET NOCOUNT ON DECLARE @strDatabase varchar(255) ,@strTypeToFind varchar(255) ,@intLength int ,@strSQLLength varchar(200) ,@bitMatchLength tinyint SELECT @bitMatchLength = 0 SELECT @strTypeToFind = 'hierarchyid' SELECT @intLength = 8000 SELECT @strSQLLength = CASE WHEN @bitMatchLength = 1 AND @intLength IS NOT NULL AND @intLength BETWEEN 1 AND 8000 AND @strTypeToFind IN ('binary','varbinary','varchar','nvarchar','char','nchar') THEN ' AND sc.max_length = ' + CONVERT(varchar(20),@intLength) ELSE '' END DECLARE @tblXML TABLE ( DatabaseName varchar(255) ,SchemaName varchar(255) ,TableName varchar(255) ,ColumnName varchar(255) ,DataType varchar(255) ,ColumnLength int ) --DECLARE crsDatabase CURSOR FOR --SELECT name --FROM master.sys.databases --ORDER BY name --OPEN crsDatabase --FETCH NEXT FROM crsDatabase INTO @strDatabase --WHILE @@FETCH_STATUS = 0 --BEGIN SELECT @strDatabase = 'AdventureWorks2012' INSERT INTO @tblXML EXEC('SELECT ' + '''' + @strDatabase + '''' + ' DatabaseName ,ss.name SchemaName ,so.name TableName ,sc.name ColumnName ,st.name DataType ,sc.max_length ColumnLength FROM [' + @strDatabase + '].sys.objects so INNER JOIN [' + @strDatabase + '].sys.schemas ss ON so.schema_id = ss.schema_id INNER JOIN [' + @strDatabase + '].sys.columns sc ON so.object_id = sc.object_id INNER JOIN [' + @strDatabase + '].sys.types st ON sc.user_type_id = st.user_type_id WHERE st.name = ' + '''' + @strTypeToFind + '''' + ' AND so.type = ' + '''' + 'u' + '''' + ' ' + @strSQLLength) -- FETCH NEXT FROM crsDatabase INTO @strDatabase --END --CLOSE crsDatabase --DEALLOCATE crsDatabase SELECT * FROM @tblXML ORDER BY DatabaseName ,TableName ,ColumnName