动态获取数据表或临时表列名
先参考这篇《获取MS SQL TABLE列名列表》https://www.cnblogs.com/insus/p/4835554.html
现在,把它改写为存储过程,动态获取任一数据表列名或者是临时表的列名。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
举例演示,先来一个获取临时表的列名:
另一个例子,是获取非临时表的列名: