MS SQL Server Concepts and Programming Question:
Download Questions PDF

How To Generate CREATE FUNCTION Script on an Existing Function?

Answer:

If you want to know how an existing user defined function was created, you can use SQL Server Management Studio to automatically generate a "CREATE FUNCTION" script The following tutorial shows you how to do this:

1. Run SQL Server Management Studio and connect to SQL server.

2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabase > Programmability > Functions > Scalar-valued Functions > dbo.Sundays.

3. Click right mouse button on dbo.Sundays. The context menu shows up.

4. Select "Script Function as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:

USE [GlobalGuideLineDatabase]
GO
/****** Object: UserDefinedFunction [dbo].[Sundays]
Script Date: 05/19/2007 23:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
<pre>CREATE FUNCTION [dbo].[Sundays]()
RETURNS INT
AS BEGIN
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date = '2006-12-31';
SET @count = 0;
WHILE DATEPART(YEAR, @date) <= 2008 BEGIN
SET @date = DATEADD(DAY, 1, @date);
IF DATENAME(WEEKDAY, @date) = 'Sunday'
SET @count = @count + 1;
END;
RETURN @count;
END;
</pre>


Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Drop an Existing User Defined Function in MS SQL Server?How To Get the Definition of a User Defined Function Back?