Home > SQL Tips > Generate SCRIPT or SOURCE CODE of DB Objects: Functions, Stored Procedures, Views, Triggers, …

Generate SCRIPT or SOURCE CODE of DB Objects: Functions, Stored Procedures, Views, Triggers, …


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/

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 380 other followers

%d bloggers like this: