Dynamic SQL to query an Adventureworks table

Posted by salvationishere on Stack Overflow See other posts from Stack Overflow or by salvationishere
Published on 2010-04-06T20:22:27Z Indexed on 2010/04/06 20:23 UTC
Read the original article Hit count: 360

Filed under:
|
|

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'

© Stack Overflow or respective owner

Related posts about dynamic-sql

Related posts about sql-server