Dynamic SQL to query an Adventureworks table
- by salvationishere
I am trying to see a list of tables from Adventureworks DB from "Person" schema in Sql Server 2008. I developed teh following SP, but after running it as follows it gives me error "Incorrect syntax near ')'". Do you know how I can revise this SP or exec statement?
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'
