咨询可复用的SQL获取指定表所有列名的代码实现

Hey there! Let's break down your current code and get it working properly for your needs.

First off, your existing snippet is truncated (ends with sys.t...) and has some unnecessary redundancy—you don't need to re-declare @SelTable inside the dynamic SQL since you already have @TableName defined outside. Plus, we can make this safer and more reliable with a few tweaks.

Issues with Your Current Code #

• Incomplete syntax (the final JOIN is cut off)
• Redundant variable declaration inside the dynamic SQL block
• No protection against SQL injection or special characters in table names
• Doesn't account for tables in non-default schemas (like schema.table instead of just table)

Improved, Safe Version (Parameterized Dynamic SQL) #

This is the best approach because it uses parameterization to avoid injection risks and clean up the code:

DECLARE @SQLCommand NVARCHAR(4000)
DECLARE @TableName VARCHAR(50) 
DECLARE @SchemaName VARCHAR(50) = 'dbo' -- Set your schema here (default is dbo)

SET @TableName = 'ship_to_ud'

-- Build the parameterized query
SET @SQLCommand = N'
SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    ac.name AS ColumnName
FROM sys.all_columns ac
INNER JOIN sys.tables t 
    ON ac.object_id = t.object_id
WHERE 
    SCHEMA_NAME(t.schema_id) = @SchemaName
    AND t.name = @TableName'

-- Execute the dynamic SQL with parameters
EXEC sp_executesql @SQLCommand, 
    N'@SchemaName VARCHAR(50), @TableName VARCHAR(50)', 
    @SchemaName = @SchemaName, 
    @TableName = @TableName

What This Does #

• Uses sp_executesql for parameterized dynamic SQL, which is far safer than string concatenation
• Includes schema name support to avoid ambiguity if you have tables with the same name in different schemas
• Returns clear, labeled columns: SchemaName, TableName, and ColumnName
• Eliminates redundant variable declarations

Alternative: String Concatenation (Less Safe, But For Legacy Use Cases) #

If you absolutely need to use string concatenation (not recommended for untrusted inputs), use QUOTENAME to handle special characters and escape single quotes:

DECLARE @SQLCommand NVARCHAR(4000)
DECLARE @TableName VARCHAR(50) 
SET @TableName = 'ship_to_ud'

SET @SQLCommand = N'
SELECT 
    t.name AS TableName,
    ac.name AS ColumnName
FROM sys.all_columns ac
INNER JOIN sys.tables t 
    ON ac.object_id = t.object_id
WHERE t.name = ' + QUOTENAME(@TableName, '''')

EXEC sp_executesql @SQLCommand

Key Notes #

• Always prefer parameterized queries (sp_executesql) over raw string concatenation to prevent SQL injection
• Including the schema name makes your query more robust, especially in databases with multiple schemas
• The sys.all_columns view includes columns from user tables, system tables, and views—if you only want user tables, use sys.columns instead (it's a subset of sys.all_columns)

内容的提问来源于stack exchange，提问作者SQL-GBH