-- -----------------------------------------------------
	-- Data Dictionary
	-- Version 1.4
	-- Produces Data Dictionary for Current Database
	-- -----------------------------------------------------

	-- Definitions
	SET NOCOUNT ON;

	DECLARE 
		@TableName nvarchar(max)
	  , @TableName2 nvarchar(max)
	  , @ColumnName  varchar(max)
	  , @ColumnName2  varchar(max)
	  , @ColumnType varchar(max)
	  , @ColumnType2 varchar(max)
	  , @MaxLength int
	  , @MaxLength2 int
	  , @IsNullable tinyint
	  , @IsNullable2 tinyint
	  , @Description varchar(max)
	  , @HasMismatch tinyint

	DECLARE @DataDictionary TABLE 
	(
		TableName varchar(max)
	  , TableDescription  varchar(max)
	  , ColumnName  varchar(max)
	  , ColumnDescription  varchar(max)
	  , IndexCount int
	  , FKCount int
	  , ColumnType varchar(max)
	  , MaxLength int
	  , Pecision int
	  , Scale int
	  , IsNullable tinyint
	  , IsComputed tinyint
	  , DefaultValue varchar(max)	
	)

	DECLARE @TableXRef TABLE 
	(
		TableNames  varchar(max)
	  , ColumnName  varchar(max)
	  , ColumnType  varchar(max)
	  , MaxLength   int
	  , IsNullable  tinyint
	  , HasMismatch tinyint
	)

	-- -----------------------------------------------------
	-- Compute Data Dictionary
	-- -----------------------------------------------------

	DECLARE Tbls CURSOR 
	FOR

	Select distinct Table_name
	FROM INFORMATION_SCHEMA.COLUMNS
	--put any exclusions here
	--where table_name not like '%old' 
	order by Table_name

	OPEN Tbls
	FETCH NEXT FROM Tbls
	INTO @TableName

	WHILE @@FETCH_STATUS = 0
	BEGIN

		Select @Description = cast(Value as varchar(max)) FROM 
		sys.extended_properties A
		WHERE A.major_id = OBJECT_ID(@TableName)
		and name = 'MS_Description' and minor_id = 0

		insert into @DataDictionary (TableName, TableDescription, ColumnName, ColumnDescription, IndexCount, FKCount, ColumnType, MaxLength, Pecision, Scale, IsNullable, IsComputed, DefaultValue)
		SELECT
			  @TableName as 'Table'
			, @Description as 'Description'
			, clmns.name
			, isnull(cast(exprop.value as varchar(max)) , '') as 'Value'
			, ISNULL(idxcol.index_column_id, 0) as 'Index'
			, ISNULL((SELECT TOP 1 1 FROM sys.foreign_key_columns AS fkclmn WHERE fkclmn.parent_column_id = clmns.column_id AND fkclmn.parent_object_id = clmns.object_id ), 0) as 'FK'
			, udt.name
			, CASE WHEN typ.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END  as 'Max Length'
			, clmns.precision 
			, clmns.scale 
			, clmns.is_nullable 
			, clmns.is_computed 
			, cnstr.definition 
			FROM sys.tables AS tbl
			INNER JOIN sys.all_columns AS clmns
			ON clmns.object_id=tbl.object_id
			LEFT OUTER JOIN sys.indexes AS idx
			ON idx.object_id = clmns.object_id AND 1 =idx.is_primary_key
			LEFT OUTER JOIN sys.index_columns AS idxcol
			ON idxcol.index_id = idx.index_id AND idxcol.column_id = clmns.column_id AND idxcol.object_id = clmns.object_id AND 0 = idxcol.is_included_column
			LEFT OUTER JOIN sys.types AS udt
			ON udt.user_type_id = clmns.user_type_id
			LEFT OUTER JOIN sys.types AS typ
			ON typ.user_type_id = clmns.system_type_id AND typ.user_type_id = typ.system_type_id
			LEFT JOIN sys.default_constraints AS cnstr
			ON cnstr.object_id=clmns.default_object_id
			LEFT OUTER JOIN sys.extended_properties exprop
			ON exprop.major_id = clmns.object_id AND exprop.minor_id = clmns.column_id AND exprop.name = 'MS_Description'
			WHERE (tbl.name = @TableName) 
				-- and (exprop.class = 1) --I don't want to include comments on indexes
			ORDER BY clmns.column_id ASC

	FETCH NEXT FROM Tbls
	INTO @TableName
	END

	CLOSE Tbls
	DEALLOCATE Tbls

	-- Show report

	select 
		TableName
	  , IsNull(TableDescription,'') TableDescription
	  , ColumnName 
	  , ColumnDescription
	  , ColumnType 
	  , MaxLength 
	  , Pecision 
	  , Scale 
	  , IsNullable 
	  , IsNull(DefaultValue , '') DefaultValue
	 from @DataDictionary
	 order by ColumnName, TableName

	-- -------------------
	-- Produce XREF
	-- -------------------

	DECLARE Tbls CURSOR 
	FOR
		Select 
		    TableName
		  ,	ColumnName
		  , ColumnType 
		  , MaxLength 
		  , IsNullable 
		From
			@DataDictionary
		Order By ColumnName

	OPEN Tbls

	FETCH NEXT FROM Tbls
	INTO 
		    @TableName
		  ,	@ColumnName
		  , @ColumnType 
		  , @MaxLength 
		  , @IsNullable 

	WHILE @@FETCH_STATUS = 0
	BEGIN

		print 'Column: ' + @ColumnName + 'Table: ' + @TableName

		set @ColumnName2 = null

		select 
			  @ColumnName2 = ColumnName
			, @TableName2 = TableNames
			, @MaxLength2 = MaxLength
			, @IsNullable2 = IsNullable
		from
			@TableXRef
		where 
			(ColumnName = @ColumnName)
		
		print '>Column2: ' + IsNull(@ColumnName2, '(null)')
		
		if(@ColumnName2 is null)
		BEGIN
			insert into @TableXRef (TableNames, ColumnName,  ColumnType,  MaxLength,  IsNullable, HasMismatch)
			values                (@TableName, @ColumnName, @ColumnType, @MaxLength, @IsNullable, 0)
			print '>>Insert'
		END
		else
		BEGIN
			if((@ColumnType <> @ColumnType2) or (@MaxLength <> @MaxLength2) or (@IsNullable <> @IsNullable2))
			begin
				update @TableXRef 
				set TableNames = @TableName2 + ', ' + @TableName, HasMisMatch = 1
				where 
						(ColumnName = @ColumnName)
				print '>>Mismatch'
			end
			else
			begin
			update @TableXRef 
				set TableNames = @TableName2 + ', ' + @TableName 
				where 
						(ColumnName = @ColumnName)
				print '>>Match'
			end
		END




	FETCH NEXT FROM Tbls
		INTO 
		    @TableName
		  ,	@ColumnName
		  , @ColumnType 
		  , @MaxLength 
		  , @IsNullable 

	END

	CLOSE Tbls
	DEALLOCATE Tbls

-- Report on Cross Reference

	Select 
		  ColumnName
		, TableNames 
		, HasMisMatch
	from @TableXRef 
	--where HasMisMatch = 1
	order by ColumnName