Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Generate a knot loading script.
- VERSION HISTORY
- 2017-04-24 Lars Rönnbäck CREATED
- */
- declare @sourceDatabase varchar(555) = 'Staging';
- declare @sourceSchema varchar(555) = 'dbo';
- select
- '/* ----------------------------------------------------------------------------' + CHAR(10) +
- 'Knot: ' + k.[name] + CHAR(10) +
- ep.metadata +
- '*/' +
- replace(
- ep.query,
- sourceTable,
- @sourceDatabase + '.' + @sourceSchema + '.' + sourceTable
- ) as [text()]
- from (
- select
- metadata,
- query,
- sourceTable,
- major_id
- from
- sys.extended_properties
- cross apply (
- values (
- cast([value] as nvarchar(max))
- )
- ) d ([description])
- cross apply (
- values (
- left([description], patindex('%Query%', [description]) - 1),
- substring([description], patindex('%Query%', [description]) + 7, 100000000),
- substring(
- [description],
- patindex('%Table%', [description]) + 7,
- charindex(CHAR(10), [description], patindex('%Table%', [description]) + 7)
- - 1
- - (patindex('%Table%', [description]) + 7)
- )
- )
- ) e (metadata, query, sourceTable)
- where
- [description] like '%Query%'
- ) ep
- join (
- select
- *,
- OBJECT_ID(name) as major_id
- from
- _Knot
- ) k
- on
- ep.major_id = k.major_id
- for xml path('')
Add Comment
Please, Sign In to add comment