How to retrieve Views and Stored Procedure statement text?

Hi,
Is there any way that I can find where the SQL server stored our Create
View or Create Procedure statement (the text), I try to find on all
system tables but can not find it.
Thanks,
RicardCREATE statements for textual objects are stored in syscomments.
Hope this helps.
Dan Guzman
SQL Server MVP
"ricard" <ricard_notrealmail@.example.com> wrote in message
news:uodgYY%23OFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all system
> tables but can not find it.
> Thanks,
> Ricard|||Hi Ricard
The stored procedure to see the definition of stored procedures and views is
sp_helptext.
If you want to see how sp_helptext gets the definition, you can look at IT'S
definition. :-)
USE master
GO
EXEC sp_helptext sp_helptext
GO
You will see that after some error checking, sp_helptext basically just does
a SELECT from the syscomments table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ricard" <ricard_notrealmail@.example.com> wrote in message
news:uodgYY%23OFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all system
> tables but can not find it.
> Thanks,
> Ricard|||ricard wrote:
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all
> system tables but can not find it.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Views:
SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view name'
Procedures and Functions:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'procedure or function name'
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQlYtxYechKqOuFEgEQId7QCcDENj6rwofI0D
eqt8hsZclqc0+McAn2Sr
g+1dfblirMiFk+jMbbf8TWZZ
=HvW0
--END PGP SIGNATURE--