dbo.vw_dba_columnnames.sql

USE [NBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   VIEW [dbo].[vw_dba_columnnames] AS

	SELECT object_id = o.object_id
		, column_id	= c.column_id
		, column_definition = l.column_definition
	FROM sys.objects o 
	JOIN sys.columns c ON o.object_id = c.object_id  
	JOIN sys.schemas s ON s.schema_id = o.schema_id  
	JOIN sys.types t  ON t.system_type_id = c.system_type_id  
	CROSS APPLY sys.dm_exec_describe_first_result_set(
		'SELECT [' + c.name + '] FROM [' + s.name + '].[' + o.name + ']'
		, NULL, 1
	) d
	LEFT JOIN (
		SELECT object_id
			, column_id
			, identity_text = CONCAT('IDENTITY(', seed, ', ', increment, ')')
		FROM sys.identity_columns
		CROSS APPLY ( SELECT seed = CAST(ISNULL(seed_value, 0) AS NVARCHAR(10))
			, increment = CAST(ISNULL(increment_value, 0) AS NVARCHAR(10)) ) s
	) i ON i.object_id = c.object_id   
		AND i.column_id = c.column_id
	LEFT JOIN sys.computed_columns u 
		ON c.object_id = u.object_id 
		AND c.column_id = u.column_id
	LEFT JOIN sys.default_constraints f
		ON c.default_object_id != 0 
		AND c.object_id = f.parent_object_id 
		AND c.column_id = f.parent_column_id
	CROSS APPLY (
		SELECT column_definition = 
			QUOTENAME(c.name) + ' ' + 
			ISNULL(
				'AS ' + u.definition
				, CONCAT_WS(' '
					, UPPER(d.system_type_name)
					, 'COLLATE ' + UPPER(c.collation_name)
					, IIF(c.is_nullable = 0, 'NOT ', '') + 'NULL'
					, 'DEFAULT ' + f.definition
					, identity_text
				)
			)
	) l

GO