If Exists Schema Fails

Could someone explain why

IF SCHEMA_ID('TestSchema') is nul CREATE SCHEMA TestSchema

Fails with Incorrect syntax near the keyword 'SCHEMA'.

Where as running the following statements

CREATE SCHEMA TestSchema

and

IF SCHEMA_ID('TestSchema') is null SELECT 1

succeeds without a problem.

Thanks

_GJK

Ok. I found the solution from

http://groups.google.com/group/microsoft.public.sqlserver.newusers/browse_thread/thread/69a4e9c45d045120/77c693f5043fbddc?lnk=st&q=schema_id&rnum=1#77c693f5043fbddc

|||

Actually the real root of your problem is that CREATE SCHEMA must be the first statement in a batch. Using dynamic SQL (as suggested in the link above) is a workaround as the dynamic statement itself is considered a new batch.

-Raul Garcia

SDE/T

SQL Server Engine

Identyfying which tables have gone into a view

I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked! When I use the schema I get some but not all of my views.

Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view?

I have used the following SQL, but unfortunately it gives too many results:

SELECT VIEWS.name AS VIEW_NAME,
TABLES.name AS TABLE_NAME,
VIEW_SQL.text
FROM sysobjects VIEWS
INNER JOIN
syscomments VIEW_SQL
ON VIEWS.id = VIEW_SQL.id
INNER JOIN
sysobjects TABLES
ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%'
WHERE (VIEWS.xtype = 'V')
AND (TABLES.xtype = 'U')
ORDER BY VIEWS.name, TABLES.name

JustinLook for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

http://www.red-gate.com/products/sql_dependency_viewer/

Regards,

hmscott|||Look for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

http://www.red-gate.com/products/sql_dependency_viewer/

Regards,

hmscott
Thanks, I have tried this tool and it should be good when its complete, however I actually want the names of the tables rather then a graph view. I am using the table names to provide some meta data for a database I am developing.

Regards
Justin