動態獲取數據表或臨時表列名

先參考這篇《獲取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

舉例演示，先來一個獲取臨時表的列名：

另一個例子，是獲取非臨時表的列名：