Generating CREATE FUNCTION Scripts on Existing Functions in SQL Server

Q

How To Generate CREATE FUNCTION Script on an Existing Function in SQL Server Transact-SQL?

✍: FYIcenter.com

A

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;

 

sys.sql_modules - Getting User Defined Function Definitions Back in SQL Server

"DROP FUNCTION" - Dropping an Existing User Defined Function in SQL Server

Using User Defined Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2016-12-18, 1732🔥, 0💬