-- DatabaseObject_V2.sql 
-- http://butzi.ca/tech

CREATE VIEW
	[dbo].[DatabaseObject]
AS
SELECT
	SysObject.id AS id
	,SysObject.xtype AS cd
	,SysObject.crdate AS createDt
	,SysObject.[name] AS nm
	,SysSchema.[name] AS schemaNm
	,LEN(SysModule.definition) AS textAmt
	,SysModule.definition AS txt
FROM
	sys.schemas AS SysSchema INNER JOIN
	sys.sysobjects AS SysObject ON SysObject.uid = SysSchema.schema_id INNER JOIN
	sys.sql_modules AS SysModule ON SysModule.object_id = SysObject.id
UNION
SELECT
	SubQuery.id AS id
	,SubQuery.typeCd AS cd
	,SubQuery.createDt AS createDt
	,SubQuery.nm AS nm
	,SubQuery.schemaNm AS schemaNm
	,LEN(SubQuery.txt) AS textAmt
	,SubQuery.txt AS txt
FROM
	(SELECT
		SysSynonym.[object_id] AS id
		,SysSynonym.create_date AS createDt
		,SysSynonym.name AS nm
		,SysSchema.name AS schemaNm
		,'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) AS SubQuery