September 22, 2008 at 6:47 pm
I would like to know if it is possible to query any mssql system table/view that would bring back meta data about the view's underlying SQL Code.
If sysobjects contained this data, the query would look something like this:
SELECT name AS vName , sqlCode --I can't seem to find this...
FROM sysobjects
WHERE xtype = 'v'
Results:
vName sqlCode
-------------------------------------------------- ------------------------------------------------------------------
vw_test SELECT a.*, b.* from tblA as a inner join tblB as b on a.id = b.id
I received a database with hundreds of views, and need to review their underlying SQL Code to better understand table relationships (No ERD at this point). It would be useful to know how to do this on 2000 and 2005. Any help would be greatly appreciated.
September 22, 2008 at 6:58 pm
Try something like:
Select * from information_schema.views
That's the 2005 version.
in 2000 - should be more something like:
select so.name,
sc.text
from syscomments sc inner join
(
select *
from sysobjects
where xtype='v'
) so on sc.id=so.id
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 22, 2008 at 7:08 pm
Matt, that's exactly what I needed. Thanks!!
September 22, 2008 at 7:09 pm
You're welcome@!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2008 at 5:42 am
Another option is (if you are on SQL server 2005) to use OBJECT_DEFINITION() function.
SELECT OBJECT_DEFINITION(OBJECT_ID('NameOfObject'))
NameofObject can be a view, stored procedure, function etc.
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply