April 21, 2011 at 12:44 pm
Hi all experts,
I have to fill a dropdown with all databases which are online and find a table into it. How do I do this? I have to list all those databases in dropdown.
Actually we are providing an application to clients which will fill the dropdown with database which has all tables and db schema matching to a list. so that user choose any one among those databases, I need a simple query which can do this.
I used following query right now do this, the trick is to only find a table among all databases:
set nocount on
CREATE TABLE master.dbo.AllTables ([Name] sysname)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + 'USE ' + QUOTENAME(name) + '
insert into master.dbo.AllTables
select ' + QUOTENAME(name,'''') + ' as [Name]
from ' +
QUOTENAME(Name) + '.sys.Tables WHERE name in (''EntityObjectMapping'');' FROM sys.databases where state != 6
ORDER BY name
EXECUTE(@SQL)
SELECT * FROM master.dbo.AllTables
if object_ID('master.dbo.AllTables','U') IS NOT NULL drop table master.dbo.AllTables
I also want to matching complete db schema too if possible, may be a user drop some other tables, sps or functions etc...
How can we do this?
Shamshad Ali
April 21, 2011 at 1:16 pm
think sp_msforeachtable and sp_msforeachdb will get you started
April 22, 2011 at 10:25 am
Shamshad Ali (4/21/2011)
Hi all experts,I have to fill a dropdown with all databases which are online and find a table into it. How do I do this? I have to list all those databases in dropdown.
Actually we are providing an application to clients which will fill the dropdown with database which has all tables and db schema matching to a list. so that user choose any one among those databases, I need a simple query which can do this.
I used following query right now do this, the trick is to only find a table among all databases:
set nocount on
CREATE TABLE master.dbo.AllTables ([Name] sysname)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + 'USE ' + QUOTENAME(name) + '
insert into master.dbo.AllTables
select ' + QUOTENAME(name,'''') + ' as [Name]
from ' +
QUOTENAME(Name) + '.sys.Tables WHERE name in (''EntityObjectMapping'');' FROM sys.databases where state != 6
ORDER BY name
EXECUTE(@SQL)
SELECT * FROM master.dbo.AllTables
if object_ID('master.dbo.AllTables','U') IS NOT NULL drop table master.dbo.AllTables
I also want to matching complete db schema too if possible, may be a user drop some other tables, sps or functions etc...
How can we do this?
Shamshad Ali
master.sys.sp_MSforeachdb will do what you need, but it is undocumented and beyond me why would you want to implement this in T-SQL when you have an application layer good at iterating over collections. You said you are building an application to display data to the user...I would do this kind of work there.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 4, 2011 at 1:34 am
Hello all, I still have this as a question because when on Production, the user context running this script does not have such rights (sysadmin) as we are deploying the solution on client's Server and they are strict to provide such permissions (sysadmin).
Now I am stuck here, what minimum rights are required to run this script or if there is any other solution is available?
The user who run this script or some other (which produce same ouput) have only public Server role provided.
Same has been implemented and addressed the issue of permission here http://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set
Please help.
Shamshad Ali.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply