SQL Server FAQ - Generating CREATE FUNCTION Scripts on Existing Functions

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Generate CREATE FUNCTION Script on an Existing Function?

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 > FyiCenterData > 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 [FyiCenterData]
GO
/****** Object:  UserDefinedFunction [dbo].[Sundays]
   Script Date: 05/19/2007 23:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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;

(Continued on next topic...)