--Put title of your documentation here (should be unique)
DECLARE @title AS NVARCHAR(250) = 'dataedo repository'; 
--table descriptions
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(tbl.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(tbl.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(tbl.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[tables] tbl
INNER JOIN dbo.[databases] db
	ON db.database_id = tbl.database_id
WHERE tbl.[description_search] IS NOT NULL
	AND tbl.[status] = 'A'
	AND tbl.[object_type] = 'TABLE'
	AND db.[title] = @title
--table column descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(tbl.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '''
		,replace(col.[name], '''', '''''')
		,''' AND [object_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(tbl.[name], '''', '''''')
		,'''))) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(col.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''COLUMN'',@level2name=N'''
		,replace(col.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(col.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''COLUMN'',@level2name=N'''
		,replace(col.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[tables] tbl
INNER JOIN dbo.[columns] col
	ON col.table_id = tbl.table_id
INNER JOIN dbo.[databases] db
	ON db.database_id = tbl.database_id
WHERE col.[description] IS NOT NULL
	AND tbl.[status] = 'A'
	AND col.[status] = 'A'
	AND tbl.[object_type] = 'TABLE'
	AND db.[title] = @title
--table trigger descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(trg.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(trg.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''TRIGGER'',@level2name=N'''
		,replace(trg.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(trg.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''TRIGGER'',@level2name=N'''
		,replace(trg.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[tables] tbl
INNER JOIN dbo.[triggers] trg
	ON trg.table_id = tbl.table_id
INNER JOIN dbo.[databases] db
	ON db.database_id = tbl.database_id
WHERE trg.[description] IS NOT NULL
	AND tbl.[status] = 'A'
	AND trg.[status] = 'A'
	AND tbl.[object_type] = 'TABLE'
	AND db.[title] = @title
--primary key descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(con.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(con.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''CONSTRAINT'',@level2name=N'''
		,replace(con.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(con.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''TABLE'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''CONSTRAINT'',@level2name=N'''
		,replace(con.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[tables] tbl
INNER JOIN dbo.[unique_constraints] con
	ON con.table_id = tbl.table_id
INNER JOIN dbo.[databases] db
	ON db.database_id = tbl.database_id
WHERE con.[description] IS NOT NULL
	AND tbl.[status] = 'A'
	AND con.[status] = 'A'
	AND con.[source] = 'DBMS'
	AND con.[primary_key] = 1
	AND tbl.[object_type] = 'TABLE'
	AND db.[title] = @title
--view descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(tbl.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(tbl.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''VIEW'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(tbl.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''VIEW'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[tables] tbl
INNER JOIN dbo.[databases] db
	ON db.database_id = tbl.database_id
WHERE tbl.[description_search] IS NOT NULL
	AND tbl.[status] = 'A'
	AND tbl.[object_type] = 'VIEW'
	AND db.[title] = @title
--view column descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(tbl.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '''
		,replace(col.[name], '''', '''''')
		,''' AND [object_id] = OBJECT_ID('''
		,replace(tbl.[schema], '''', '''''')
		,'.'
		,replace(tbl.[name], '''', '''''')
		,'''))) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(col.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''VIEW'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''COLUMN'',@level2name=N'''
		,replace(col.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(col.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(tbl.[schema], '''', '''''')
		,''', @level1type=N''VIEW'',@level1name=N'''
		,replace(tbl.[name], '''', '''''')
		,''', @level2type=N''COLUMN'',@level2name=N'''
		,replace(col.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[tables] tbl
INNER JOIN dbo.[columns] col
	ON col.table_id = tbl.table_id
INNER JOIN dbo.[databases] db
	ON db.database_id = tbl.database_id
WHERE col.[description] IS NOT NULL
	AND tbl.[status] = 'A'
	AND tbl.[object_type] = 'VIEW'
	AND db.[title] = @title
--procedure descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(prc.[schema], '''', '''''')
		,'.'
		,replace(prc.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(prc.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''PROCEDURE'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(prc.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''PROCEDURE'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[procedures] prc
INNER JOIN dbo.[databases] db
	ON db.database_id = prc.database_id
WHERE prc.[description_search] IS NOT NULL
	AND prc.[status] = 'A'
	AND prc.[object_type] = 'PROCEDURE'
	AND db.[title] = @title
--procedure parameter descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(prc.[schema], '''', '''''')
		,'.'
		,replace(prc.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = (SELECT [parameter_id] FROM SYS.PARAMETERS WHERE [name] = ''@'
		,replace(par.[name], '''', '''''')
		,''' AND [object_id] = OBJECT_ID('''
		,replace(prc.[schema], '''', '''''')
		,'.'
		,replace(prc.[name], '''', '''''')
		,'''))) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(par.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''PROCEDURE'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''', @level2type=N''PARAMETER'',@level2name=N''@'
		,replace(par.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(par.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''PROCEDURE'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''', @level2type=N''PARAMETER'',@level2name=N''@'
		,replace(par.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[procedures] prc
INNER JOIN dbo.[databases] db
	ON db.database_id = prc.database_id
INNER JOIN dbo.[parameters] par
	ON prc.procedure_id=par.procedure_id
WHERE par.[description] IS NOT NULL
	AND prc.[status] = 'A'
	AND par.[status] = 'A'
	AND prc.[object_type] = 'PROCEDURE'
	AND db.[title] = @title
--function descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(prc.[schema], '''', '''''')
		,'.'
		,replace(prc.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = 0) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(prc.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''FUNCTION'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(prc.[description_search], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''FUNCTION'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[procedures] prc
INNER JOIN dbo.[databases] db
	ON db.database_id = prc.database_id
WHERE prc.[description_search] IS NOT NULL
	AND prc.[status] = 'A'
	AND prc.[object_type] = 'FUNCTION'
	AND db.[title] = @title
--function parameter descriptions
UNION ALL
SELECT CONCAT (
		'IF NOT EXISTS (SELECT 1  FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('''
		,replace(prc.[schema], '''', '''''')
		,'.'
		,replace(prc.[name], '''', '''''')
		,''') AND [name] = N''MS_Description'' AND [minor_id] = (SELECT [parameter_id] FROM SYS.PARAMETERS WHERE [name] = ''@'
		,replace(par.[name], '''', '''''')
		,''' AND [object_id] = OBJECT_ID('''
		,replace(prc.[schema], '''', '''''')
		,'.'
		,replace(prc.[name], '''', '''''')
		,'''))) '
		,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(par.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''FUNCTION'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''', @level2type=N''PARAMETER'',@level2name=N''@'
		,replace(par.[name], '''', '''''')
		,''' ELSE '
		,'EXEC sys.sp_updateextendedproperty @name=N''MS_Description'', @value=N'''
		,replace(left(par.[description], 3498), '''', '''''')
		,''' , @level0type=N''SCHEMA'',@level0name=N'''
		,replace(prc.[schema], '''', '''''')
		,''', @level1type=N''FUNCTION'',@level1name=N'''
		,replace(prc.[name], '''', '''''')
		,''', @level2type=N''PARAMETER'',@level2name=N''@'
		,replace(par.[name], '''', '''''')
		,''';'
		) AS tsql_command
FROM dbo.[procedures] prc
INNER JOIN dbo.[databases] db
	ON db.database_id = prc.database_id
INNER JOIN dbo.[parameters] par
	ON prc.procedure_id=par.procedure_id
WHERE par.[description] IS NOT NULL
	AND prc.[status] = 'A'
	AND par.[status] = 'A'
	AND par.[ordinal_position]<>0
	AND prc.[object_type] = 'FUNCTION'
	AND db.[title] = @title;