SQL with Manoj

The metadata in SQL Server contains the information and code of the Database Objects, like Functions, Stored Procedures, Views, Triggers, etc.

Following are the 3 main ways you can get or generate Script or Source Code of these DB Objects:

USE [AdventureWorks]
GO
-- Method #1
SELECT ROUTINE_DEFINITION, ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ufnGetContactInformation';
GO
-- Method #2
select c.text, object_name(c.id), o.type
from sys.syscomments c
join sys.sysobjects o
on o.id = c.id
where c.id = object_ID('ufnGetContactInformation');
GO
<p>-- Method #3
exec sp_helptext 'dbo.ufnGetContactInformation';
GO

More about SQL Server metadata: https://sqlwithmanoj.wordpress.com/2010/12/06/querying-sql-server-metadata/