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.
views:
59answers:
3
+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
2010-02-05 14:48:56
@Raj - hope you don't mind, corrected InformationSchema to Information_Schema
AdaTheDev
2010-02-05 14:59:23
SELECT table_name FROM information_schema.tablesWHERE table_catalog = @databasename and table_type = 'BASE TABLE'
zapping
2010-02-05 15:05:59
@zapping - the information_schema runs within the context of the current database. It will return the tables for that db only.
AdaTheDev
2010-02-05 15:23:27
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
2010-02-08 07:02:16
+1
A:
If your database is Northwind for example
SELECT name FROM Northwind..sysobjects WHERE type='U'
gd047
2010-02-05 14:53:34
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
2010-02-05 15:03:04
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
2010-02-05 15:04:20
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
2010-02-05 15:03:31
I need to specify the name of the databse in the clause WHERE to get the name of its tables in my query
CREFLY
2010-02-05 15:17:51
@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
2010-02-05 15:22:29