sql to find database of a table mssql

Question

I am working on a project that has multiple contexts because data are being pulled form different databases.

We have around hundreds of databases, 1 database have hundreds of tables too, so going through a lot of databases are there to find a table is painstaking.

I only have code in finding a table name from a specific database through column name like this:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%mycolumn%'
ORDER BY    TableName
            ,ColumnName;

But sometimes I already know the table name but don't know which database it belongs to. Can anyone point me to the right code?

UPDATE:

I meant to say database. for a clearer explanation, here is our connectionstring, as you can see I just added few databases.

But, it can grow based on what data we need in our codes. I need the database name that will serve as my context (I'm using dbscopecontext) as well as be able to query like this

select * from [MyDatabase].[dbo].[MyTable]

Because just selecting from the query pad like below gives an error.

select * from [dbo].[MyTable] 


    <add name="EContext" connectionString="server; Database=E;  providerName="System.Data.SqlClient" />
    <add name="MContext" connectionString="server; Database=M;  providerName="System.Data.SqlClient" />
    <add name="AContext" connectionString="server; Database=A;  providerName="System.Data.SqlClient" />
    <add name="BaContext" connectionString="server; Database=B;  providerName="System.Data.SqlClient" />
    <add name="WConntextntext" connectionString="server; Database=W;  providerName="System.Data.SqlClient" />
    <add name="WpContext" connectionString="server; Database=Wp;  providerName="System.Data.SqlClient" />
    <add name="Maontext" connectionString="server; Database=MA;  providerName="System.Data.SqlClient" />
    <add name="BlsContext" connectionString="server; Database=Bls;  providerName="System.Data.SqlClient" />
    <add name="BiContext" connectionString="server; Database=Bi;  providerName="System.Data.SqlClient" />
</connectionStrings>

Thanks in advance

Data sources are normally set at application level. If you want to know the data source something is using, why not look at the application, and find out the connection string it is using? Trying to find an object, by name, which could be on any number of servers, in any number of databases is the wrong way to go about this. — Thom A, Nov 15 '18 at 11:00
The same data source that you used to run your quoted sql statement? — RegBes, Nov 15 '18 at 11:33
sorry I meant to say database not datasource. I updated my question. thanks — user742102, Nov 15 '18 at 12:02
The connection string list you posted - what is it for? Entity Framework? To find a table can't you just do a global search in your generated classes? — Nick.Mc, Nov 15 '18 at 12:16
In general, this question can't be answered because databases are allowed to use the same table names without conflict -- there could be a `Foo` in every single database and you'd have no clue which one was intended without also knowing the connection string used. You can get a list of all tables in all databases with `sp_msforeachdb 'select ''?'', name from [?].sys.tables where is_ms_shipped = 0'`. Finding *a* table this way could be done with `sp_msforeachdb 'if exists (select * from sys.tables where name = ''Foo'') select ''?'''`. I'd recommend neither as a systematic approach, though. — Jeroen Mostert, Nov 15 '18 at 12:19
It is for both EF and non EF. I am the one who started the project (converting a legacy project to a new asp mvc proj) and still doing it, so I am the one creating the classes. I am not using edmx or tt so I don't know where exactly the entities are, even if I do, I'd still have to know which edmx/database I need to add so I can access a certain table, which is my very problem. — user742102, Nov 15 '18 at 12:26

score 1 · Answer 1 · answered Nov 15 '18 at 12:33

1

Try this query and modify as per your need. All you need to pass only column name in the query and this will list all tables and databases names.

DECLARE @SysQuery NVARCHAR(MAX)
DECLARE @columnName NVARCHAR(100) = 'EmailID'
SELECT
    @SysQuery = isnull(@SysQuery+N' UNION ALL ',N'')+
'
SELECT [Database] = convert(sysname,'''+a.name+
'''), [Table] = a.name collate SQL_Latin1_General_CP1_CI_AS from '+quotename(a.name)+'.sys.tables a 
INNER JOIN' +quotename(a.name)+'.sys.columns c ON  c.OBJECT_ID = a.OBJECT_ID AND c.name like ''%'+@columnName+'%'''
FROM
    sys.databases a
PRINT @SysQuery

EXEC ( @SysQuery+ ' ORDER by 1,2' )

answered Nov 15 '18 at 12:33

Rahul Neekhra

780
1
9
39

Msg 945, Level 14, State 2, Line 2 Database 'WJ..' cannot be opened due to inaccessible files or insufficient memory or disk space.I ran it using "Master" – user742102 Nov 15 '18 at 13:02
Contact your database administrator as it is system level problem not code level. – Rahul Neekhra Nov 15 '18 at 18:39

score 0 · Answer 2 · answered Nov 15 '18 at 18:40

Problem: Msg 945, Level 14, State 2, Line 2 Database 'WJ..' cannot be opened due to inaccessible files or insufficient memory or disk space.I ran it using "Master"

Fix/Solution/Work arounds:

1) If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size.

2) Check if the database is set to Autogrow on.

3) Check if the account which is trying to access the database has enough permission to perform operation.

4) Make sure that .mdf and .ldf file are not marked as read only on operating system file system level.

sql to find database of a table mssql

2 Answers2