views:

59

answers:

3

I need to write a querry in SQL SERVER 2005 to get the name of the tables of a specified database name. So i need the syntax to specify the name of the database using SQL SERVER 2005. Does anyone have any idea ? Thanks in advance for your help.

+1  A: 
use DatabaseName;

SELECT *
FROM Information_Schema.Tables

Or specify the name of the database in your connection string and run

SELECT *
FROM Information_Schema.Tables

Or use a fully qualified name

SELECT *
FROM DatabaseName.Information_Schema.Tables

In your case, try this

SELECT name As descriptionCommande from YOURDATABASENAME.sys.Views

OR

USE YOURDATABASENAME
SELECT name As descriptionCommande from sys.Views
Raj More
@Raj - hope you don't mind, corrected InformationSchema to Information_Schema
AdaTheDev
SELECT table_name FROM information_schema.tablesWHERE table_catalog = @databasename and table_type = 'BASE TABLE'
zapping
@zapping - the information_schema runs within the context of the current database. It will return the tables for that db only.
AdaTheDev
thanks zapping. I execute your query but I didn't receive any data...To be more descriptive , this is my query :SELECT sys.Views.name As descriptionCommandeFROM sys.ViewsI just need to specify the name of the database in a where clause using vb code so I need the syntax of the query to specify the name of the database
CREFLY
+1  A: 

If your database is Northwind for example

SELECT name FROM Northwind..sysobjects WHERE type='U'
gd047
correct me if I am wrong but this sentence will also return the sysdiagrams table so watch out since you may not need it. for the rest the query will work.
Goows
it does work I need specify the name of the database that i created on the server for ex: SELECT sys.Tables.name FROM sys.Tables INNER Join sys.sql_modules ON sys.Tables.object_ID = sys.sql_modules.object_idinner join sys.databases on sys.Tables.object_ID = sys.databases.where ........... = 'Databasename'
CREFLY
You want to use the xtype field: "WHERE xtype = 'U'"
vincebowdren
A: 

I could be wrong, but it sounds like you want to specify the name dynamically, in which case you need to use dynamic sql:

DECLARE @DatabaseName NVARCHAR(128)
SET @DatabaseName = 'DatabaseName'

-- Safety check to make sure the database is a valid db name
IF EXISTS(SELECT * FROM master..sysdatabases WHERE name = @DatabaseName)
 BEGIN
  DECLARE @nSQL NVARCHAR(200)
  SET @nSQL = 'SELECT * FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLES'
  EXECUTE sp_executesql @nSQL
 END
AdaTheDev
I need to specify the name of the databse in the clause WHERE to get the name of its tables in my query
CREFLY
@CREFLY - you can't specify it in a WHERE clause - there is no central list of tables for all databases. Hence, you have to look in the appropriate database itself. Unless you create a separate job to iterate round each db periodically and pull out a list of tables into somewhere central.
AdaTheDev