ビュー、関数、ストアドプロシージャのSQLを取得するsp_helptext [SQLServer]

データベースに作成済みのビュー、関数、ストアドプロシージャなどのSQLを確認したい場合には、SQL Server Management Studioのオブジェクトエクスプローラーで対象のオブジェクトを探し出して表示し、オブジェクトを右クリックして表示されるコンテキストメニューの[○○をスクリプト化] > [新規作成] > [新しいクエリエディターウィンドウ]をクリックすることでソースのSQLを表示することができます。

しかし、わざわざオブジェクトエクスプローラーのツリービューを展開していってオブジェクトを探すのは、オブジェクトの数が増えてくると面倒なものです。

今回は、Transact-SQLのクエリエディターでビュー、関数、ストアドプロシージャなどのSQLを取得して表示する方法を紹介します。

本記事ではデータの取得、表示にMicrosoftが公開しているデータベースの「AdventureWorks2017」を使用しています。

1 クエリエディターでオブジェクトのSQLを取得して表示
2 テーブルの計算列の数式を取得して表示

クエリエディターでオブジェクトのSQLを取得して表示

クエリエディターでビュー、関数、ストアドプロシージャなどのオブジェクトのSQLを表示するには、システムストアドプロシージャのsp_helptextを使用します。

sp_helptextは以下の構文で使用します。

[EXEC] sp_helptext [@objname = ] 'オブジェクト名'
-- EXEC はあってもなくてもOK
-- オブジェクト名はデータベース名、スキーマ名を指定しない場合は「'(シングルクォート)」で囲っても囲わなくてもOK
-- 「'(シングルクォート)」で囲っているオブジェクト名は「[]（各括弧）」で囲ってもよい

[EXEC] sp_helptext [@objname = ] 'オブジェクト名'

-- EXEC はあってもなくてもOK

-- オブジェクト名はデータベース名、スキーマ名を指定しない場合は「'(シングルクォート)」で囲っても囲わなくてもOK

-- 「'(シングルクォート)」で囲っているオブジェクト名は「[]（各括弧）」で囲ってもよい

ビューのSQLを取得して表示

以下の例ではデータベース[AdventureWorks2017]のビュー[HumanResources.vEmployee]のSQLを取得しています。

-- HumanResources.vEmployeeビューのSQLを取得
-- オブジェクト名をN''で囲って取得
EXEC sp_helptext N'AdventureWorks2017.HumanResources.vEmployee';
GO
-- オブジェクト名を[]で囲って取得
EXEC sp_helptext [AdventureWorks2017.HumanResources.vEmployee];
GO

-- HumanResources.vEmployeeビューのSQLを取得

-- オブジェクト名をN''で囲って取得

EXEC sp_helptext N'AdventureWorks2017.HumanResources.vEmployee';

-- オブジェクト名を[]で囲って取得

EXEC sp_helptext [AdventureWorks2017.HumanResources.vEmployee];

sp_helptextの実行結果

取得結果のSQLテキストを掲載しておきます。

CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]  
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON p.[BusinessEntityID] = ea.[BusinessEntityID];

CREATE VIEW [HumanResources].[vEmployee]

SELECT

e.[BusinessEntityID]

,p.[Title]

,p.[FirstName]

,p.[MiddleName]

,p.[LastName]

,p.[Suffix]

,e.[JobTitle]

,pp.[PhoneNumber]

,pnt.[Name] AS [PhoneNumberType]

,ea.[EmailAddress]

,p.[EmailPromotion]

,a.[AddressLine1]

,a.[AddressLine2]

,a.[City]

,sp.[Name] AS [StateProvinceName]

,a.[PostalCode]

,cr.[Name] AS [CountryRegionName]

,p.[AdditionalContactInfo]

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Person] p

ON p.[BusinessEntityID] = e.[BusinessEntityID]

INNER JOIN [Person].[BusinessEntityAddress] bea

ON bea.[BusinessEntityID] = e.[BusinessEntityID]

INNER JOIN [Person].[Address] a

ON a.[AddressID] = bea.[AddressID]

INNER JOIN [Person].[StateProvince] sp

ON sp.[StateProvinceID] = a.[StateProvinceID]

INNER JOIN [Person].[CountryRegion] cr

ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

LEFT OUTER JOIN [Person].[PersonPhone] pp

ON pp.BusinessEntityID = p.[BusinessEntityID]

LEFT OUTER JOIN [Person].[PhoneNumberType] pnt

ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]

LEFT OUTER JOIN [Person].[EmailAddress] ea

ON p.[BusinessEntityID] = ea.[BusinessEntityID];

関数のSQLを取得して表示

以下の例ではデータベース[AdventureWorks2017]の関数[dbo.ufnGetContactInformation]のSQLを取得しています。

-- ufnGetContactInformation関数のSQLを取得
sp_helptext ufnGetContactInformation;

1 2	-- ufnGetContactInformation関数のSQLを取得 sp_helptext ufnGetContactInformation;

sp_helptextの実行結果

取得結果のSQLテキストを掲載しておきます。

CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    [PersonID] int NOT NULL, 
    [FirstName] [nvarchar](50) NULL, 
    [LastName] [nvarchar](50) NULL, 
	[JobTitle] [nvarchar](50) NULL,
    [BusinessEntityType] [nvarchar](50) NULL
)
AS 
-- Returns the first name, last name, job title and business entity type for the specified contact.
-- Since a contact can serve multiple roles, more than one row may be returned.
BEGIN
	IF @PersonID IS NOT NULL 
		BEGIN
		IF EXISTS(SELECT * FROM [HumanResources].[Employee] e 
					WHERE e.[BusinessEntityID] = @PersonID) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
				FROM [HumanResources].[Employee] AS e
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = e.[BusinessEntityID]
				WHERE e.[BusinessEntityID] = @PersonID;

		IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact' 
				FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;
		
		IF EXISTS(SELECT * FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact' 
				FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;

		IF EXISTS(SELECT * FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer' 
				FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; 
		END

	RETURN;
END;

CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

[PersonID] int NOT NULL,

[FirstName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL,

[JobTitle] [nvarchar](50) NULL,

[BusinessEntityType] [nvarchar](50) NULL

)

-- Returns the first name, last name, job title and business entity type for the specified contact.

-- Since a contact can serve multiple roles, more than one row may be returned.

BEGIN

IF @PersonID IS NOT NULL

BEGIN

IF EXISTS(SELECT * FROM [HumanResources].[Employee] e

WHERE e.[BusinessEntityID] = @PersonID)

INSERT INTO @retContactInformation

SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'

FROM [HumanResources].[Employee] AS e

INNER JOIN [Person].[Person] p

ON p.[BusinessEntityID] = e.[BusinessEntityID]

WHERE e.[BusinessEntityID] = @PersonID;

IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v

INNER JOIN [Person].[BusinessEntityContact] bec

ON bec.[BusinessEntityID] = v.[BusinessEntityID]

WHERE bec.[PersonID] = @PersonID)

INSERT INTO @retContactInformation

SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact'

FROM [Purchasing].[Vendor] AS v

INNER JOIN [Person].[BusinessEntityContact] bec

ON bec.[BusinessEntityID] = v.[BusinessEntityID]

INNER JOIN [Person].ContactType ct

ON ct.[ContactTypeID] = bec.[ContactTypeID]

INNER JOIN [Person].[Person] p

ON p.[BusinessEntityID] = bec.[PersonID]

WHERE bec.[PersonID] = @PersonID;

IF EXISTS(SELECT * FROM [Sales].[Store] AS s

INNER JOIN [Person].[BusinessEntityContact] bec

ON bec.[BusinessEntityID] = s.[BusinessEntityID]

WHERE bec.[PersonID] = @PersonID)

INSERT INTO @retContactInformation

SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact'

FROM [Sales].[Store] AS s

INNER JOIN [Person].[BusinessEntityContact] bec

ON bec.[BusinessEntityID] = s.[BusinessEntityID]

INNER JOIN [Person].ContactType ct

ON ct.[ContactTypeID] = bec.[ContactTypeID]

INNER JOIN [Person].[Person] p

ON p.[BusinessEntityID] = bec.[PersonID]

WHERE bec.[PersonID] = @PersonID;

IF EXISTS(SELECT * FROM [Person].[Person] AS p

INNER JOIN [Sales].[Customer] AS c

ON c.[PersonID] = p.[BusinessEntityID]

WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL)

INSERT INTO @retContactInformation

SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer'

FROM [Person].[Person] AS p

INNER JOIN [Sales].[Customer] AS c

ON c.[PersonID] = p.[BusinessEntityID]

WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL;

END

RETURN;

END;

ストアドプロシージャのSQLを取得して表示

以下の例ではデータベース[AdventureWorks2017]の関数[dbo.uspGetBillOfMaterials]のSQLを取得しています。

-- uspGetBillOfMaterialsプロシージャのSQLを取得
sp_helptext [dbo.uspGetBillOfMaterials];

1 2	-- uspGetBillOfMaterialsプロシージャのSQLを取得 sp_helptext [dbo.uspGetBillOfMaterials];

sp_helptextの実行結果

取得結果のSQLテキストを掲載しておきます。

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this date.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE b.[ProductAssemblyID] = @StartProductID 
            AND @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b 
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25) 
END;

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]

@StartProductID [int],

@CheckDate [datetime]

BEGIN

SET NOCOUNT ON;

-- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1

-- components of a level 0 assembly, all level 2 components of a level 1 assembly)

-- The CheckDate eliminates any components that are no longer used in the product on this date.

WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns

AS (

SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly

FROM [Production].[BillOfMaterials] b

INNER JOIN [Production].[Product] p

ON b.[ComponentID] = p.[ProductID]

WHERE b.[ProductAssemblyID] = @StartProductID

AND @CheckDate >= b.[StartDate]

AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)

UNION ALL

SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor

FROM [BOM_cte] cte

INNER JOIN [Production].[BillOfMaterials] b

ON b.[ProductAssemblyID] = cte.[ComponentID]

INNER JOIN [Production].[Product] p

ON b.[ComponentID] = p.[ProductID]

WHERE @CheckDate >= b.[StartDate]

AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)

)

-- Outer select from the CTE

SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]

FROM [BOM_cte] b

GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]

ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]

OPTION (MAXRECURSION 25)

END;

sp_helptextではシステムプロシージャのSQLも取得して表示することができます。

以下の例ではsp_helptextでsp_helptext自身のSQLを取得しています。

-- sp_helptextのSQLを取得
EXEC sp_helptext sp_helptext;

1 2	-- sp_helptextのSQLを取得 EXEC sp_helptext sp_helptext;

sp_helptextの実行結果

取得結果のSQLテキストを掲載しておきます。

create procedure sys.sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
as

set nocount on

declare @dbname sysname
,@objid	int
,@BlankSpaceAdded   int
,@BasePos       int
,@CurrentPos    int
,@TextLength    int
,@LineId        int
,@AddOnLen      int
,@LFCR          int --lengths of line feed carriage return
,@DefinedLength int

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText	nvarchar(4000)
,@Line          nvarchar(255)

select @DefinedLength = 255
select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
                             trailing blank spaces*/
CREATE TABLE #CommentText
(LineId	int
 ,Text  nvarchar(255) collate catalog_default)

/*
**  Make sure the @objname is local to the current database.
*/
select @dbname = parsename(@objname,3)
if @dbname is null
	select @dbname = db_name()
else if @dbname <> db_name()
        begin
                raiserror(15250,-1,-1)
                return (1)
        end

/*
**  See if @objname exists.
*/
select @objid = object_id(@objname)
if (@objid is null)
        begin
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
        end

-- If second parameter was given.
if ( @columnname is not null)
    begin
        -- Check if it is a table
        if (select count(*) from sys.objects where object_id = @objid and type in ('S ','U ','TF'))=0
            begin
                raiserror(15218,-1,-1,@objname)
                return(1)
            end
        -- check if it is a correct column name
        if ((select 'count'=count(*) from sys.columns where name = @columnname and object_id = @objid) =0)
            begin
                raiserror(15645,-1,-1,@columnname)
                return(1)
            end
    if (ColumnProperty(@objid, @columnname, 'IsComputed') = 0)
		begin
			raiserror(15646,-1,-1,@columnname)
			return(1)
		end

        declare ms_crs_syscom  CURSOR LOCAL
        FOR select text from syscomments where id = @objid and encrypted = 0 and number =
                        (select column_id from sys.columns where name = @columnname and object_id = @objid)
                        order by number,colid
        FOR READ ONLY

    end
else if @objid < 0	-- Handle system-objects
	begin
		-- Check count of rows with text data
		if (select count(*) from master.sys.syscomments where id = @objid and text is not null) = 0
			begin
				raiserror(15197,-1,-1,@objname)
				return (1)
			end
			
		declare ms_crs_syscom CURSOR LOCAL FOR select text from master.sys.syscomments where id = @objid
			ORDER BY number, colid FOR READ ONLY
	end
else
    begin
        /*
        **  Find out how many lines of text are coming back,
        **  and return if there are none.
        */
        if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
            and o.id = c.id and o.id = @objid) = 0
                begin
                        raiserror(15197,-1,-1,@objname)
                        return (1)
                end

        if (select count(*) from syscomments where id = @objid and encrypted = 0) = 0
                begin
                        raiserror(15471,-1,-1,@objname)
                        return (0)
                end

		declare ms_crs_syscom  CURSOR LOCAL
		FOR select text from syscomments where id = @objid and encrypted = 0
				ORDER BY number, colid
		FOR READ ONLY

    end

/*
**  else get the text.
*/
select @LFCR = 2
select @LineId = 1


OPEN ms_crs_syscom

FETCH NEXT from ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
begin

    select  @BasePos    = 1
 select  @CurrentPos = 1
    select  @TextLength = LEN(@SyscomText)

    WHILE @CurrentPos  != 0
    begin
        --Looking for end of line followed by carriage return
        select @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

        --If carriage return found
        IF @CurrentPos != 0
        begin
            /*If new value for @Lines length will be > then the
            **set length then insert current contents of @line
            **and proceed.
            */
            while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
            begin
                select @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                INSERT #CommentText VALUES
                ( @LineId,
                  isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                select @Line = NULL, @LineId = @LineId + 1,
                       @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
            end
            select @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
            select @BasePos = @CurrentPos+2
            INSERT #CommentText VALUES( @LineId, @Line )
            select @LineId = @LineId + 1
            select @Line = NULL
        end
        else
        --else carriage return not found
        begin
            IF @BasePos <= @TextLength
            begin
                /*If new value for @Lines length will be > then the
                **defined length
                */
                while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                begin
                    select @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
                    INSERT #CommentText VALUES
                    ( @LineId,
                      isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                    select @Line = NULL, @LineId = @LineId + 1,
                        @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
                end
                select @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
                if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
                begin
                    select @Line = @Line + ' ', @BlankSpaceAdded = 1
                end
            end
        end
    end

	FETCH NEXT from ms_crs_syscom into @SyscomText
end

IF @Line is NOT NULL
    INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE  ms_crs_syscom
DEALLOCATE 	ms_crs_syscom

DROP TABLE 	#CommentText

return (0) -- sp_helptext

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

create procedure sys.sp_helptext

@objname nvarchar(776)

,@columnname sysname = NULL

set nocount on

declare @dbname sysname

,@objid int

,@BlankSpaceAdded int

,@BasePos int

,@CurrentPos int

,@TextLength int

,@LineId int

,@AddOnLen int

,@LFCR int --lengths of line feed carriage return

,@DefinedLength int

/* NOTE: Length of @SyscomText is 4000 to replace the length of

** text column in syscomments.

** lengths on @Line, #CommentText Text column and

** value for @DefinedLength are all 255. These need to all have

** the same values. 255 was selected in order for the max length

** display using down level clients

,@SyscomText nvarchar(4000)

,@Line nvarchar(255)

select @DefinedLength = 255

select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores

trailing blank spaces*/

CREATE TABLE #CommentText

(LineId int

,Text nvarchar(255) collate catalog_default)

** Make sure the @objname is local to the current database.

select @dbname = parsename(@objname,3)

if @dbname is null

select @dbname = db_name()

else if @dbname <> db_name()

begin

raiserror(15250,-1,-1)

return (1)

end

** See if @objname exists.

select @objid = object_id(@objname)

if (@objid is null)

begin

raiserror(15009,-1,-1,@objname,@dbname)

return (1)

end

-- If second parameter was given.

if ( @columnname is not null)

begin

-- Check if it is a table

if (select count(*) from sys.objects where object_id = @objid and type in ('S ','U ','TF'))=0

begin

raiserror(15218,-1,-1,@objname)

return(1)

end

-- check if it is a correct column name

if ((select 'count'=count(*) from sys.columns where name = @columnname and object_id = @objid) =0)

begin

raiserror(15645,-1,-1,@columnname)

return(1)

end

if (ColumnProperty(@objid, @columnname, 'IsComputed') = 0)

begin

raiserror(15646,-1,-1,@columnname)

return(1)

end

declare ms_crs_syscom CURSOR LOCAL

FOR select text from syscomments where id = @objid and encrypted = 0 and number =

(select column_id from sys.columns where name = @columnname and object_id = @objid)

order by number,colid

FOR READ ONLY

end

else if @objid < 0 -- Handle system-objects

begin

-- Check count of rows with text data

if (select count(*) from master.sys.syscomments where id = @objid and text is not null) = 0

begin

raiserror(15197,-1,-1,@objname)

return (1)

end

declare ms_crs_syscom CURSOR LOCAL FOR select text from master.sys.syscomments where id = @objid

ORDER BY number, colid FOR READ ONLY

end

else

begin

** Find out how many lines of text are coming back,

** and return if there are none.

if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')

and o.id = c.id and o.id = @objid) = 0

begin

raiserror(15197,-1,-1,@objname)

return (1)

end

if (select count(*) from syscomments where id = @objid and encrypted = 0) = 0

begin

raiserror(15471,-1,-1,@objname)

return (0)

end

declare ms_crs_syscom CURSOR LOCAL

FOR select text from syscomments where id = @objid and encrypted = 0

ORDER BY number, colid

FOR READ ONLY

end

** else get the text.

select @LFCR = 2

select @LineId = 1

OPEN ms_crs_syscom

FETCH NEXT from ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0

begin

select @BasePos = 1

select @CurrentPos = 1

select @TextLength = LEN(@SyscomText)

WHILE @CurrentPos != 0

begin

--Looking for end of line followed by carriage return

select @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

--If carriage return found

IF @CurrentPos != 0

begin

/*If new value for @Lines length will be > then the

**set length then insert current contents of @line

**and proceed.

while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength

begin

select @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)

INSERT #CommentText VALUES

( @LineId,

isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))

select @Line = NULL, @LineId = @LineId + 1,

@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0

end

select @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')

select @BasePos = @CurrentPos+2

INSERT #CommentText VALUES( @LineId, @Line )

select @LineId = @LineId + 1

select @Line = NULL

end

else

--else carriage return not found

begin

IF @BasePos <= @TextLength

begin

/*If new value for @Lines length will be > then the

**defined length

while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength

begin

select @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)

INSERT #CommentText VALUES

( @LineId,

isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))

select @Line = NULL, @LineId = @LineId + 1,

@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0

end

select @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')

if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0

begin

select @Line = @Line + ' ', @BlankSpaceAdded = 1

end

FETCH NEXT from ms_crs_syscom into @SyscomText

end

IF @Line is NOT NULL

INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE ms_crs_syscom

DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText

return (0) -- sp_helptext

テーブルの計算列の数式を取得して表示

sp_helptextではテーブルの計算列の数式を取得して表示することもできます。

計算列の情報を取得する際はsp_helptextを以下の構文で使用します。

[EXEC] sp_helptext [@objname = ] 'オブジェクト名' [ @columnname = ] '列名'
-- 列名は「'(シングルクォート)」で囲っても囲わなくてもOK

1 2	[EXEC] sp_helptext [@objname = ] 'オブジェクト名' [ @columnname = ] '列名' -- 列名は「'(シングルクォート)」で囲っても囲わなくてもOK

以下の例ではデータベース[AdventureWorks2017]のテーブル[Sales.SalesOrderHeader]の計算列である[SalesOrderNumber]と[TotalDue]の数式を取得しています。

-- SalesOrderNumber列の計算式を取得
EXEC sp_helptext N'AdventureWorks2017.Sales.SalesOrderHeader', SalesOrderNumber;
GO
-- TotalDue列の計算式を取得
EXEC sp_helptext N'AdventureWorks2017.Sales.SalesOrderHeader', TotalDue;
GO

-- SalesOrderNumber列の計算式を取得

EXEC sp_helptext N'AdventureWorks2017.Sales.SalesOrderHeader', SalesOrderNumber;

-- TotalDue列の計算式を取得

EXEC sp_helptext N'AdventureWorks2017.Sales.SalesOrderHeader', TotalDue;

sp_helptextの実行結果

SQL Server Management Studioのテーブルデザイナーで列を選択した時に表示される列のプロパティにある[計算列の指定]に入力されている数式が取得されます。

-- SalesOrderNumber列の計算式
(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***'))
-- TotalDue列の計算式
(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))

-- SalesOrderNumber列の計算式

(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***'))

-- TotalDue列の計算式

(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))

sp_helptextプロシージャはクエリエディターを操作しながら既存のオブジェクトのSQLを取得して表示することができるので、例えば既存のオブジェクトのSQLを拡張して新しいオブジェクトを作成する場合などにクエリエディターから離れることなく拡張元のSQLを取得して加工ができるので使い方によっては作業効率がグッと上がります。

参考URL
sp_helptext (TRANSACT-SQL)