/*******************************************************************
*	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