retrieving View's SQL statement [modified]
-
hi how should i retrieve View's SQL statement in SQL Server. i am using OleDb and i want it to be compatible with all the data Systems. for example i can retrieve the Query statements from an Access database (with GetOleDbSchemaGuid() ) but i am not able to do it with SQL Server (it dowsnt support retrieving views that way). is there another way or a specific SQL statement to do so?? thanks in advance -- modified at 6:20 Monday 26th February, 2007
-
hi how should i retrieve View's SQL statement in SQL Server. i am using OleDb and i want it to be compatible with all the data Systems. for example i can retrieve the Query statements from an Access database (with GetOleDbSchemaGuid() ) but i am not able to do it with SQL Server (it dowsnt support retrieving views that way). is there another way or a specific SQL statement to do so?? thanks in advance -- modified at 6:20 Monday 26th February, 2007
I used profiler to create a trace of all the queries from SSMS to the database and came up with this:
SELECT ISNULL(smv.definition, ssmv.definition) AS [Definition] FROM sys.all_views 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(v.name=N'{my_view_name}' and SCHEMA_NAME(v.schema_id)=N'dbo')Just replace {my_view_name} with the name of the view you're interested in. Also, if the view is using a schema other than 'dbo' make sure to change that as well.