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

先参考这篇《获取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

 

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

 

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

 

另一个例子，是获取非临时表的列名：