find Different Database tables and Database in One Query

Posted by Dotnet4ashu under Sql Server category on | Points: 40 | Views : 1326
Hello, all Sql Experts,

You May also use this query to find out different tables and Databases through one query.

For Example

For Finding Databases

Select * from sys.databases

For Finding Tables

use database

Select * from sys.tables

Comments or Responses

Posted by: PandianS on: 2/14/2012 Level:Silver | Status: [Member] [MVP] | Points: 10

You can use to fetch all the Databases and It's user tables...
Create Table #AllTables

DBName Varchar(100),
TableName Varchar(128)

Insert #AllTables
Exec Sp_MsforeachDB 'Use [?]; Select DB_Name(),Name from sys.all_objects Where type=''U'' And is_ms_shipped =0'

Select * from #AllTables

Drop Table #AllTables

Posted by: Jish on: 3/5/2012 Level:Starter | Status: [Member] | Points: 10
i tried it..

Login to post response