SET NOCOUNT ON;
--/*-- ========================================
-- Job
-- Server/JobServer/Job[@Name='{1}']
SELECT
urn = N'Server/JobServer/Job[@Name='
+ QUOTENAME(name, N'''')
+ N']',
script_file = NULL
FROM msdb.dbo.sysjobs JOB WITH(NOLOCK)
WHERE JOB.category_id IN( -- not include replication job
SELECT CAT.category_id
FROM msdb.dbo.syscategories CAT WITH(NOLOCK)
WHERE CAT.category_class = 1
AND CAT.name NOT IN(N'Log Shipping')
AND CAT.name NOT LIKE N'REPL-%'
)
AND enabled = 1 -- not include disable job
;
-- ======================================== */
/*-- ========================================
-- Schema
-- Server/Database[@Name='{0}']/Schema[@Name='{1}']
SELECT
urn = N'Server'
+ N'/Database[@Name='
+ QUOTENAME(DB_NAME(), N'''')
+ N']'
+ N'/Schema[@Name='
+ QUOTENAME(OBJ.name, N'''')
+ N']',
script_file = NULL
FROM sys.schemas OBJ WITH(NOLOCK)
INNER JOIN sys.database_principals DP WITH(NOLOCK)
ON DP.principal_id = OBJ.principal_id
WHERE DP.type IN(
'S',
'U', 'G',
'C', 'K'
)
AND OBJ.name NOT IN(
N'dbo', N'sys', N'guest',
N'INFORMATION_SCHEMA'
)
AND OBJ.name NOT LIKE N'%Mark%Delete%'
AND NOT(
OBJ.name = DP.name
AND OBJ.name LIKE N'%dbo'
)
;
-- ======================================== */
/*-- ========================================
-- tables
-- Server/Database[@Name='{0}']/Table[@Name='{1}' and @Schema='{2}']
SELECT
urn = N'Server'
+ N'/Database[@Name='
+ QUOTENAME(DB_NAME(), N'''')
+ N']'
+ N'/Table[@Name='
+ QUOTENAME(OBJ.name, N'''')
+ N' and @Schema='
+ QUOTENAME(SCH.name, N'''')
+ N']',
script_file = SCH.name
+ N'.'
+ OBJ.name
FROM sys.tables OBJ WITH(NOLOCK)
INNER JOIN sys.schemas SCH WITH(NOLOCK)
ON SCH.schema_id = OBJ.schema_id
WHERE OBJ.is_ms_shipped = 0
AND OBJ.name NOT LIKE N'%Mark%Delete%'
--AND SCH.name = N'dbo'
;
-- ======================================== */
/*-- ========================================
-- views
-- Server/Database[@Name='{0}']/View[@Name='{1}' and @Schema='{2}']
SELECT
urn = N'Server'
+ N'/Database[@Name='
+ QUOTENAME(DB_NAME(), N'''')
+ N']'
+ N'/View[@Name='
+ QUOTENAME(OBJ.name, N'''')
+ N' and @Schema='
+ QUOTENAME(SCH.name, N'''')
+ N']',
script_file = SCH.name
+ N'.'
+ OBJ.name
FROM sys.views OBJ WITH(NOLOCK)
INNER JOIN sys.schemas SCH WITH(NOLOCK)
ON SCH.schema_id = OBJ.schema_id
WHERE OBJ.is_ms_shipped = 0
AND OBJ.name NOT LIKE N'%Mark%Delete%'
--AND SCH.name = N'dbo'
;
-- ======================================== */
/*-- ========================================
-- functions
-- Server/Database[@Name='{0}']/UserDefinedFunction[@Name='{1}' and @Schema='{2}']
SELECT
urn = N'Server'
+ N'/Database[@Name='
+ QUOTENAME(DB_NAME(), N'''')
+ N']'
+ N'/UserDefinedFunction[@Name='
+ QUOTENAME(OBJ.name, N'''')
+ N' and @Schema='
+ QUOTENAME(SCH.name, N'''')
+ N']',
script_file = SCH.name
+ N'.'
+ OBJ.name
FROM sys.objects OBJ WITH(NOLOCK)
INNER JOIN sys.schemas SCH WITH(NOLOCK)
ON SCH.schema_id = OBJ.schema_id
WHERE OBJ.type IN(
'TF', 'FN', 'IF', 'FS', 'FT',
'AF'
)
AND OBJ.is_ms_shipped = 0
AND OBJ.name NOT LIKE N'%Mark%Delete%'
--AND SCH.name = N'dbo'
;
-- ======================================== */
/*-- ========================================
-- procedures
-- Server/Database[@Name='{0}']/StoredProcedure[@Name='{1}' and @Schema='{2}']
SELECT
urn = N'Server'
+ N'/Database[@Name='
+ QUOTENAME(DB_NAME(), N'''')
+ N']'
+ N'/StoredProcedure[@Name='
+ QUOTENAME(OBJ.name, N'''')
+ N' and @Schema='
+ QUOTENAME(SCH.name, N'''')
+ N']',
script_file = SCH.name
+ N'.'
+ OBJ.name
FROM sys.procedures OBJ WITH(NOLOCK)
INNER JOIN sys.schemas SCH WITH(NOLOCK)
ON SCH.schema_id = OBJ.schema_id
WHERE OBJ.is_ms_shipped = 0
AND OBJ.name NOT LIKE N'%Mark%Delete%'
--AND SCH.name = N'dbo'
;
-- ======================================== */