DECLARE @TMP_xml TABLE ( vl xml); DECLARE @TMP_path TABLE ( dbpath nvarchar(256)); DECLARE @TMP_FP TABLE (type_desc NVARCHAR(256), name NVARCHAR(256), vfp nvarchar(max)); insert into @TMP_FP SELECT distinct o.type_desc , Schema_name(Schema_id)+'.'+OBJECT_NAME(Parent_object_id)+'.'+o.name AS name , (o.type_desc) collate SQL_Latin1_General_CP1_CI_AS+'#,#'+Schema_name(Schema_id)+'#,#'+('('+OBJECT_NAME(Parent_object_id)+')'+o.name) collate SQL_Latin1_General_CP1_CI_AS +'#,#'+CONVERT(VARCHAR(32) , HASHBYTES('MD5',CONVERT(VARchar(8000), SUBSTRING(reverse(SUBSTRING(Reverse(isnull(sm.[DEFINITION],'')), PATINDEX('%[A-z0-9]%', lower(REVERSE(isnull(sm.[DEFINITION],'')))), len(isnull(sm.[DEFINITION],'')))), CHARINDEX(lower(o.name), lower(isnull(sm.[DEFINITION],''))), len(isnull(sm.[DEFINITION],'')))) ) ,2) AS vfp FROM [sys].[objects] o INNER JOIN [sys].sql_modules sm ON o.object_id = sm.OBJECT_ID WHERE o.Type = 'TR' ORDER BY o.type_desc , Schema_name(Schema_id)+'.'+OBJECT_NAME(Parent_object_id)+'.'+o.name ; insert into @TMP_FP SELECT distinct o.type_desc,Schema_name(Schema_id)+'.'+so.name as name, (o.type_desc) collate SQL_Latin1_General_CP1_CI_AS+'#,#'+Schema_name(Schema_id)+'#,#'+(so.name) collate SQL_Latin1_General_CP1_CI_AS+'#,#'+CONVERT(VARCHAR(32) , HASHBYTES('MD5', CONVERT(VARchar(8000),SUBSTRING(reverse(SUBSTRING(Reverse(isnull(sr_r.ROUTINE_DEFINITION,'')), PATINDEX('%[A-z0-9]%', lower(REVERSE(isnull(sr_r.ROUTINE_DEFINITION,'')))), len(isnull(sr_r.ROUTINE_DEFINITION,'')))), CHARINDEX(lower(so.name), lower(isnull(sr_r.ROUTINE_DEFINITION,''))), len(isnull(sr_r.ROUTINE_DEFINITION,'')))) ) ,2) as vfp FROM [dbo].sysobjects so left JOIN [sys].objects o ON so.id=o.object_id left join INFORMATION_SCHEMA.ROUTINES sr_r on so.name = sr_r.ROUTINE_NAME WHERE so.type IN ('P', 'FN', 'IF', 'TF','V' ) ORDER BY o.type_desc , Schema_name(Schema_id)+'.'+so.name ; /*---*/ insert into @TMP_FP select '','', 'Queue#,#'+ OBJECT_SCHEMA_NAME(object_id)+'#,#'+name as vfp from sys.service_queues insert into @TMP_FP SELECT '','', 'Service#,#'+OBJECT_SCHEMA_NAME(service_queue_id)+'#,#'+ OBJECT_NAME(service_queue_id) +'#,#'+ name as vfp FROM sys.services where name not like 'http://schemas.microsoft.com%' insert into @TMP_FP select '','', 'Contract#,#'+isnull(OBJECT_SCHEMA_NAME(service_contract_id),'NULL')+'#,#'+name as vfp from sys.service_contracts where name not like 'http://schemas.microsoft.com%' insert into @TMP_FP select '','', 'Message#,#'+name as vfp from sys.service_message_types where name not like 'http://schemas.microsoft.com%' insert into @TMP_FP select '','', 'PK#,#'+schema_name(tab.schema_id) +'#,#'+ tab.[name]+ '#,#columns#,#'+ substring(column_names, 1, len(column_names)-1) as vfp from sys.tables tab inner join sys.indexes pk on tab.object_id = pk.object_id and pk.is_primary_key = 1 cross apply (select col.[name] + ', ' from sys.index_columns ic inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id where ic.object_id = tab.object_id and ic.index_id = pk.index_id order by col.column_id for xml path ('') ) D (column_names) order by schema_name(tab.schema_id), tab.[name] insert into @TMP_FP SELECT '','', 'FK#,#'+ (schema_name(tab1.schema_id))+'#,#'+tab1.name+'#,#'+col1.name+'#,#reference#,#'+(schema_name(tab2.schema_id))+'#,#'+tab2.name+'#,#'+col2.name as vfp FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id order by schema_name(tab1.schema_id), tab1.[name] /*---*/ insert into @TMP_xml select (select vfp from @TMP_FP FOR XML PATH('')) ; DECLARE @TMP_T TABLE (TABLE_SCHEMA NVARCHAR(256), TABLE_NAME NVARCHAR(256), ORDINAL_POSITION NVARCHAR(256),vt nvarchar(max)) insert into @TMP_T select distinct c.TABLE_SCHEMA, c.TABLE_NAME, ORDINAL_POSITION , c.TABLE_SCHEMA +'#,#'+c.TABLE_NAME +'#,#'+ COLUMN_NAME +'#,#'+ DATA_TYPE +'#,#'+ isnull(cast (CHARACTER_MAXIMUM_LENGTH as nvarchar(128)),' ') +'#,#'+ CAST(CASE c.IS_NULLABLE WHEN 'NO' THEN 'NOT_NULL' ELSE 'IS_NULL' END AS nvarchar(12)) +'#,#'+ CAST(CASE (COLUMNPROPERTY(OBJECT_ID('[' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + ']') , c.COLUMN_NAME, 'IsIdentity')) WHEN '0' THEN 'Not_Identity' ELSE 'IsIdentity' END AS nvarchar(12)) +'#,#'+ isnull(cast (COLUMN_DEFAULT as nvarchar(1024)),'NULL') +'#,#'+ isnull(cast (COLLATION_NAME as nvarchar(128)),'NULL') +'#,#'+ cast (c.ORDINAL_POSITION as nvarchar(128)) as v from INFORMATION_SCHEMA.COLUMNS c join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME = t.TABLE_NAME WHERE t.TABLE_TYPE = 'BASE TABLE' AND t.TABLE_CATALOG=(SELECT db_name()) order by c.TABLE_SCHEMA ,c.TABLE_NAME , ORDINAL_POSITION; /* insert into @TMP_T SELECT distinct schema_name(st.schema_id) as TABLE_SCHEMA, st.name as TABLE_NAME ,sal.[column_id] as ORDINAL_POSITION, 'TT_'+ schema_name(st.schema_id) +'#,#'+st.name +'#,#'+ sal.[name] +'#,#'+ sst.name +'#,#'+ isnull(cast (sal.[max_length] as nvarchar(128)),' ') +'#,#'+ CAST(CASE sal.[is_nullable] WHEN '1' THEN 'NOT_NULL' ELSE 'IS_NULL' END AS nvarchar(12)) +'#,#'+ CAST(CASE ([is_identity]) WHEN '0' THEN 'Not_Identity' ELSE 'IsIdentity' END AS nvarchar(12)) +'#,#'+/*COLUMN_DEFAULT*/'NULL' +'#,#'+ isnull(cast (sal.[collation_name] as nvarchar(128)),'NULL') +'_'+ sao.name +'#,#'+ cast (sal.[column_id] as nvarchar(128)) as v FROM sys.all_columns sal join sys.all_objects sao on sao.object_id = sal.object_id left join sys.types st on sao.name like N'TT_'+st.name+'_%' left join sys.systypes sst on sal.system_type_id = sst.xtype where sao.type in ('TT') order by TABLE_SCHEMA ,TABLE_NAME , ORDINAL_POSITION; */ insert into @TMP_xml select (select vt from @TMP_T FOR XML PATH('')) ; insert into @TMP_path values (''+(select @@servername+'\'+DB_NAME()+'---'+cast(getdate() as nvarchar(256) ) )) ; insert into @TMP_xml select (select dbpath from @TMP_path FOR XML PATH('')) ; select vl from @TMP_xml FOR XML PATH(''); select * from @TMP_path;Select ('Схема базы ' + db_name())