动态获取数据表或临时表列名

时间:2022-03-07 04:03:43

先参考这篇《获取MS SQL TABLE列名列表https://www.cnblogs.com/insus/p/4835554.html

 

现在,把它改写为存储过程,动态获取任一数据表列名或者是临时表的列名。

动态获取数据表或临时表列名

 

动态获取数据表或临时表列名动态获取数据表或临时表列名
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-21
-- Update date: 2019-05-21
-- Description: 动态获取数据表或临时表的列名
-- =============================================
CREATE PROCEDURE [dbo].[usp_Retrieve_Column_Name]
(
    @TABLE_CATALOG SYSNAME,
    @TABLE_SCHEMA SYSNAME,
    @TABLE_NAME SYSNAME
)
AS
BEGIN    
    DECLARE @query_sql NVARCHAR(MAX) = N''
    IF EXISTS(SELECT TOP 1 1 FROM [tempdb].[dbo].[sysobjects] o WHERE o.[xtype] IN ('U') AND o.[id] = object_id(@TABLE_CATALOG + N'.'+ @TABLE_SCHEMA + N'.' + @TABLE_NAME))
        SET @query_sql = N'SELECT [name] FROM [tempdb].[sys].[columns] WHERE object_id = object_id('''+ @TABLE_CATALOG + N'.'+ @TABLE_SCHEMA + N'.' + @TABLE_NAME +''')'
    ELSE
        SET @query_sql = N'SELECT [name] FROM [sys].[columns] WHERE object_id = object_id('''+ @TABLE_CATALOG + N'.'+ @TABLE_SCHEMA + N'.' + @TABLE_NAME +''')'
    EXECUTE sp_executesql @query_sql
END
GO
Source Code

 

以上代码中,有判断临时表是否存在,这个判断方法,可以参考这篇《判断临时表是否存在https://www.cnblogs.com/insus/p/10899365.html

 

举例演示,先来一个获取临时表的列名:

动态获取数据表或临时表列名

 

另一个例子,是获取非临时表的列名:

动态获取数据表或临时表列名