Problem with script that queries for table list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Problem with script that queries for table list

Author

Topic

SilentCodingOne
Starting Member

20 Posts

Posted - 2008-12-07 : 02:17:35

I'm working on a script that is to generate a select query that will bring back a list of all of the tables in the HumanResources schema of the AdventureWorks database. When I run the script I get the following error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Schema Name'.

Here is my code and I was hoping someone could help:


DECLARE @sql VARCHAR(255)
DECLARE @schema VARCHAR(255)

SET @schema = 'HumanResources'

SET @sql =  (s.name[Schema Name], t.name[Table Name]
FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id
			WHERE s.name = 'HumanResources')

SELECT ‘@sql’

EXEC(@sql)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 02:22:37

why use dynamic sql? you need only this


DECLARE @schema VARCHAR(255)

SET @schema = 'HumanResources'

SELECT s.name AS [Schema Name], t.name AS [Table Name]
FROM sys.schemas s 
INNER JOIN sys.tables t 
ON s.schema_id = t.schema_id
WHERE s.name = @schema

SilentCodingOne
Starting Member

20 Posts

Posted - 2008-12-07 : 02:52:33

quote:

Originally posted by visakh16

why use dynamic sql? you need only this


DECLARE @schema VARCHAR(255)

SET @schema = 'HumanResources'

SELECT s.name AS [Schema Name], t.name AS [Table Name]
FROM sys.schemas s 
INNER JOIN sys.tables t 
ON s.schema_id = t.schema_id
WHERE s.name = @schema

Unfortunately it is a school assignment which is requiring the dynamic sql usage

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 04:34:17

quote:
Originally posted by SilentCodingOne

quote:
Originally posted by visakh16

why use dynamic sql? you need only this

DECLARE @schema VARCHAR(255)

SET @schema = 'HumanResources'

SELECT s.name AS [Schema Name], t.name AS [Table Name]
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
WHERE s.name = @schema

Unfortunately it is a school assignment which is requiring the dynamic sql usage

didnt understand why they ask to overcomplicate things when it can be done in much simpler way.


DECLARE @sql VARCHAR(255)
DECLARE @schema VARCHAR(255)

SET @schema = 'HumanResources'

SET @sql = 'SELECT s.name[Schema Name], t.name[Table Name]
FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.name = ' + @schema

PRINT (@sql)

EXEC(@sql)

SilentCodingOne
Starting Member

20 Posts

Posted - 2008-12-07 : 22:57:06

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 23:25:00

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources