System Views text in SQL Server 2005

Question

Hello Friends

I am looking for viewing the text of the system views and procedures in SQL Server 2005 using the object explorer or using sp_helptext.

actually i am coming from the SQL Server 2000 background, where we have the feature of retreiving the code of the view and the stored procedure using SQL Server 2000 Enterprise manager directly, but still i am unable to find this feature in the SQL Server 2005 Management Studio and still looking for the same feature for getting the view and procedure text in SQL Server 2005.

Kindly please help me in this.

Answer 1

Do you have access to SQL Server Management Studio? It is now sys.sp_helptext and can be browsed at master --> Programmability --> Stored Procedures --> System Stored Procedures in the object browser and executed with

exec sys.sp_helptext ObjectName

All the information you are looking for can be found in the syscomments table which stores the definitions of all views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints, and stored procedures. The SQL definition statements are stored in the "text" column.

select text from syscomments  where id =
OBJECT_id('objectname')
order by colid

Answer 2

This Stackoverflow posting has a database reverse engineering script that (amongst other things) reverse engineers view definitions. From the script

-- This generates view definitions 
--
select definition + char(10) + 'go' + char(10)
  from sys.sql_modules c
  join sys.objects o
    on c.object_id = o.object_id
  join #views o2
    on o.object_id = o2.object_id

Note that #views is populated earlier in the script with a list of views to dump out. To select for a particular schema (also from the script)

select o.name
      ,o.object_id
  into #views
  from sys.objects o
  join sys.schemas s
    on s.schema_id = o.schema_id
 where o.type in  ('V')
   and s.name = @schema

To get stored procedures, substitute 'P'; to get functions substitute 'FN' or 'TF'

In the master database the definitions for the system stored procedures live in sys.system_views, 'sys.system_objects, 'sys.system_columns, and sys.system_sql_modules. The queries in the reverse engineering script could be adapted fairly readily to get out the definitions of the system items by using these tables.

Answer 3

If you just want to see the text that defines a procedure:

Right click the stored procedure, and choose Modify. This will show the SQL that defines the stored procedure.

Or: right click, Script Stored Procedure as, CREATE To, New Query Editor Window.

The other answers are more advanced but I thought maybe you were asking a simple question :)

Answer 4

Here's how to list all the Dynamic Management Views:

SELECT * FROM sysobjects
WHERE name LIKE 'dm_%'
order by name

Unfortunately, if you run sp_helptext on these it doesn't give you much. For example,

exec sp_helptext   N'sys.dm_os_sys_info'

Returns:

CREATE VIEW sys.dm_os_sys_info AS
    SELECT *
    FROM OpenRowset(TABLE SYSINFO)

All the ones I tried gave the same result.