用于查询Adventureworks表的动态SQL

Question

我正在尝试从Sql Server2008中的"Person“架构查看Adventureworks DB中的表列表。我开发了下面的SP，但是按照下面的方式运行之后，它给出了错误“‘)’附近的语法不正确”。您知道如何修改此SP或exec语句吗？

CREATE PROCEDURE [getTableNames]
@SchemaName VARCHAR(50)
AS

BEGIN
SET NOCOUNT ON;
SET @SchemaName = 'PERSON'
DECLARE @cmd AS VARCHAR(max)
SET @SchemaName = RTRIM(@SchemaName)
SET @cmd = N'SELECT TABLE_NAME ' + 
'FROM information_schema.Tables ' +
'WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_SCHEMA = @SchemaName'
END

exec sp_executesql getTableNames, N'@SchemaName NVARCHAR(50), @SchemaName'

Answer 1

您不需要动态SQL：

select * from sys.tables
where type_desc = 'BASE TABLE' and schema_id = schema_id(@SchemaName)

Answer 2

您实际上不需要在这里使用动态SQL，而且您的sproc并不是很正确，因为您没有执行@cmd语句。只需使用：

CREATE PROCEDURE [getTableNames]
@SchemaName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON
    SELECT TABLE_NAME 
    FROM information_schema.Tables
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = @SchemaName
END

EXECUTE getTableNames 'PERSON'