Egyszerű kis open source projekt, amely SMO segítségével leszkripteli az adatbázis objektumokat.

Ha másra nem, hát SMO példának jól jöhet.

A sebessége egyébként meglepően gyalázatos, 12 perc alatt végzett az AdventureWorks-szel, 800 objektummal (jó, a replikáció be van benne izzítva, az ad hozzá sok objektumot).

Megnéztem már munka közben profilerrel, mert érdekelt, miért ilyen lassú. Nos, minden egyes objektumhoz lefuttatott egy jó nagy rendszertáblákra vonatkozó lekérdezést, amelyek végrehajtási ideje 100 és 500 ms között volt. Ezt beszorozva 800-zal kijön a fenti idő.

Ízelítőül egy 650 ms-os állatfajta:

[source:SQL]
SELECT
SCHEMA_NAME(v.schema_id) AS [View_Schema],
v.name AS [View_Name],
tr.name AS [Name],
trr.is_instead_of_trigger AS [InsteadOf],
CAST(ISNULL(tei.object_id,0) AS bit) AS [Insert],
CAST(ISNULL(ted.object_id,0) AS bit) AS [Delete],
CAST(ISNULL(teu.object_id,0) AS bit) AS [Update],
CASE WHEN tr.type = N’TR’ THEN 1 WHEN tr.type = N’TA’ THEN 2 ELSE 1 END AS [ImplementationType],
CAST(OBJECTPROPERTYEX(tr.object_id,N’ExecIsAnsiNullsOn’) AS bit) AS [AnsiNullsStatus],
CAST(OBJECTPROPERTYEX(tr.object_id,N’ExecIsQuotedIdentOn’) AS bit) AS [QuotedIdentifierStatus],
NULL AS [Text],
CAST(
tr.is_ms_shipped
AS bit) AS [IsSystemObject],
CASE WHEN ted.is_first = 1 THEN 0 WHEN ted.is_last = 1 THEN 2 ELSE 1 END AS [DeleteOrder],
CASE WHEN tei.is_first = 1 THEN 0 WHEN tei.is_last = 1 THEN 2 ELSE 1 END AS [InsertOrder],
CASE WHEN teu.is_first = 1 THEN 0 WHEN teu.is_last = 1 THEN 2 ELSE 1 END AS [UpdateOrder],
ISNULL(smtr.definition, ssmtr.definition) AS [Definition]
FROM
sys.all_views AS v
INNER JOIN sys.objects AS tr ON (tr.type in (‘TR’, ‘TA’)) AND (tr.parent_object_id=v.object_id)
LEFT OUTER JOIN sys.assembly_modules AS mod ON mod.object_id = tr.object_id
INNER JOIN sys.triggers AS trr ON trr.object_id = tr.object_id
LEFT OUTER JOIN sys.trigger_events AS tei ON tei.object_id = tr.object_id and tei.type=1
LEFT OUTER JOIN sys.trigger_events AS ted ON ted.object_id = tr.object_id and ted.type=3
LEFT OUTER JOIN sys.trigger_events AS teu ON teu.object_id = tr.object_id and teu.type=2
LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id
WHERE
(v.type = ‘V’)and(v.name=N’MSmerge_ppmv_B78A4C9C984B46D6BD69FEF04493340F’
and SCHEMA_NAME(v.schema_id)=N’dbo’)
ORDER BY
[View_Schema] ASC,[View_Name] ASC,[Name] ASC
[/source]

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.