-- DatabaseObject.sql
-- butzi.ca/tech

CREATE VIEW
	[dbo].[DatabaseObject]
AS
SELECT
	SysObject.id AS id
	,SysObject.crdate AS createDt
	,SysObject.[name] AS nm
	,SysSchema.[name] AS schemaNm
	,LEN(SubQuery.txt) AS textAmt
	,SubQuery.txt AS txt
	,SysObject.xtype AS typeCd
FROM
	sys.schemas AS SysSchema INNER JOIN
	sys.sysobjects AS SysObject ON SysObject.uid = SysSchema.schema_id INNER JOIN
	(SELECT
		CAST(SubQuery1.txt AS NVARCHAR(MAX)) +
			CAST(ISNULL(SubQuery2.txt, '') AS NVARCHAR(MAX)) +
			CAST(ISNULL(SubQuery3.txt, '') AS NVARCHAR(MAX)) +
			CAST(ISNULL(SubQuery4.txt, '') AS NVARCHAR(MAX)) +
			CAST(ISNULL(SubQuery5.txt, '') AS NVARCHAR(MAX)) AS txt
		,SubQuery1.id AS id_SysObject
	FROM
		(SELECT DISTINCT
			SysComments.id AS id
			,SysComments.[text] AS txt
		FROM
			syscomments AS SysComments
		WHERE
			SysComments.colid = 1) AS SubQuery1 LEFT OUTER JOIN
		(SELECT DISTINCT
			SysComments.id AS id
			,SysComments.[text] AS txt
		FROM
			SysComments
		WHERE
			SysComments.colid = 2) AS SubQuery2 ON SubQuery2.id = SubQuery1.id LEFT OUTER JOIN
		(SELECT DISTINCT
			SysComments.id AS id
			,SysComments.[text] AS txt
		FROM
			SysComments
		WHERE
			SysComments.colid = 3) AS SubQuery3 ON SubQuery3.id = SubQuery1.id LEFT OUTER JOIN
		(SELECT DISTINCT
			SysComments.id AS id
			,SysComments.[text] AS txt
		FROM
			SysComments
		WHERE
			SysComments.colid = 4) AS SubQuery4 ON SubQuery4.id = SubQuery1.id LEFT OUTER JOIN
		(SELECT DISTINCT
			SysComments.id AS id
			,SysComments.[text] AS txt
		FROM
			SysComments
		WHERE
			SysComments.colid = 5) AS SubQuery5 ON SubQuery5.id = SubQuery1.id) AS SubQuery ON SubQuery.id_SysObject = SysObject.id
UNION
SELECT
	SysSynonym.[object_id] AS id
	,SysSynonym.create_date AS createDt
	,SysSynonym.name AS nm
	,SysSchema.name AS schemaNm
	,LEN('CREATE SYNONYM [' +  SysSchema.name + '].[' + SysSynonym.name + '] FOR ' + SysSynonym.base_object_name) AS textAmt
	,'CREATE SYNONYM [' +  SysSchema.name + '].[' + SysSynonym.name + '] FOR ' + SysSynonym.base_object_name AS txt
	,'SN' AS typeCd
FROM
	sys.schemas AS SysSchema INNER JOIN
	sys.synonyms AS SysSynonym ON SysSynonym.schema_id = SysSchema.schema_id