I just see this topic. it's very similar to my question. but i don't want to use any third party for creating the script. i want to create the script of dropping and creating the views of a database in dependency order and programmatically. how i can do such a thing ?
-
first, be very precise when you define dependency order of views - then query the data dictionary for those rules, then produce a script from the language of your choice. then post what you have tried and what does not work.Randy– Randy2012-11-14 13:15:27 +00:00Commented Nov 14, 2012 at 13:15
-
thanks, i just want to do what you say, can you explain it more, i just tried ssms, scriptio and other Microsoft tool that is like the ssms generate script and i dont remember the name. can you or someone else describe what you say in detail, thanks.Farzad Karimi– Farzad Karimi2012-11-14 13:29:08 +00:00Commented Nov 14, 2012 at 13:29
-
i found a way like this to create the commands, but it's without Dependency :Farzad Karimi– Farzad Karimi2012-11-17 13:08:39 +00:00Commented Nov 17, 2012 at 13:08
-
SELECT 'DROP VIEW ' + v.name As DropCmd, RTRIM(ISNULL(smv.definition, ssmv.definition) ) AS CreateCmd FROM sys AS v LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id WHERE v.type = 'V' AND SCHEMA_NAME(v.schema_id) = N'dbo' ORDER BY v.nameFarzad Karimi– Farzad Karimi2012-11-20 06:25:22 +00:00Commented Nov 20, 2012 at 6:25
Add a comment
|
1 Answer
At last I Found My Answer to be like this, But Remember that this is just for views that has one nesting level. not more ! :
SELECT MIN(Row) AS Row, CreateCmd FROM (
SELECT Row_Number() OVER (ORDER BY S.Row) As Row, S.CreateCmd FROM (
SELECT 0 AS Row, RTRIM(ISNULL(smv.definition, ssmv.definition)) AS CreateCmd
FROM sys.all_objects AS v
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id
INNER JOIN (SELECT object_id, referenced_major_id FROM sys.sql_dependencies
GROUP BY object_id, referenced_major_id) AS a ON v.object_id = a.referenced_major_id
WHERE (v.type = 'V' OR v.type = 'P' OR v.type = 'IF' OR v.type = 'TF' OR v.type = 'FN')
AND SCHEMA_NAME(v.schema_id) = N'dbo' AND is_ms_shipped <> 1
AND smv.execute_as_principal_id IS NULL AND ssmv.execute_as_principal_id IS NULL
GROUP BY v.Name, smv.definition, ssmv.definition
UNION ALL
SELECT 1 AS Row, RTRIM(ISNULL(smv.definition, ssmv.definition)) AS CreateCmd
FROM sys.all_objects AS v
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id
INNER JOIN (SELECT object_id, referenced_major_id FROM sys.sql_dependencies
GROUP BY object_id, referenced_major_id) AS a ON v.object_id = a.object_id
WHERE (v.type = 'V' OR v.type = 'P' OR v.type = 'IF' OR v.type = 'TF' OR v.type = 'FN')
AND SCHEMA_NAME(v.schema_id) = N'dbo' AND is_ms_shipped <> 1
AND smv.execute_as_principal_id IS NULL AND ssmv.execute_as_principal_id IS NULL
GROUP BY v.Name, smv.definition, ssmv.definition
) S GROUP BY S.Row, CreateCmd
) D GROUP BY CreateCmd ORDER BY Row