Как найти таблицу в sql server

Время на прочтение
5 мин

Количество просмотров 62K

Описание общей потребности в поиске данных и объектов в базе данных

Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.

Достаточно часто может возникнуть ситуация, при которой нужно найти:

  1. объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
  2. данные (значение и в какой таблице располагается)
  3. фрагмент кода в определениях объектов базы данных

Существует множество готовых решений как платных, так и бесплатных.

Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.

Поиск с помощью встроенных средств самой СУБД

Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:

Поиск таблицы по имени

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name]='Employee';

Результат может быть примерно такой:

Здесь выводятся:

  1. идентификаторы объекта и схемы, где располагается объект
  2. название этой схемы и название этого объекта
  3. тип объекта и описание этого типа объекта
  4. даты и время создания и последней модификации объекта

Чтобы найти все вхождения строки “Project”, то можно использовать следующий скрипт:

Поиск всех объектов по подстроке в имени

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name] like '%Project%';

Результат может быть примерно такой:

Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.

Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:

Поиск всех объектов по подстроке в имени с выводом родительских объектов

select ao.[object_id], ao.[schema_id],
	   schema_name(ao.[schema_id]) as [schema_name],
	   ao.parent_object_id,
	   p.[schema_id] as [parent_schema_id],
	   schema_name(p.[schema_id]) as [parent_schema_name],
	   p.[name] as [parent_name],
	   ao.[name], 
	   ao.[type], 
	   ao.[type_desc], 
	   ao.[create_date], 
	   ao.[modify_date]
from sys.all_objects as ao
left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
where ao.[name] like '%Project%';

Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:

В запросах используются следующие системные объекты:

  • таблица sys.all_objects
  • скалярная функция schema_name

Итак, разобрали как найти объекты в базе данных с помощью встроенных средств самой СУБД.
Теперь покажем как найти данные в базе данных на примере поиска строк.

Чтобы найти строковое значение по всем таблицам базы данных, можно воспользоваться следующим решением. Упростим данное решение и покажем как можно найти например значение “Ramiro” с помощью следующего скрипта:

Поиск строковых значений по подстроке во всех таблицах базы данных

set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'

declare @sql nvarchar(max);

create table #rslt 
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
 declare c cursor for 
	select quotename(column_name) as column_name from information_schema.columns 
	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name  = @name
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   --print 'Processing table - ' + @name + ', column - ' + @column

   set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column + 
	' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';

	print @sql;

   exec(@sql);

   fetch next from c into @column;
 end
 close c
 deallocate c
 fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name

drop table #rslt
close s
deallocate s

Результат выполнения может быть таким:

Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.

Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:

  1. sys.sql_modules
  2. sys.all_sql_modules
  3. sys.syscomments

Например, используя последнее представление, можно с помощью следующего скрипта найти все объекты, в определениях которых встречается заданный фрагмент кода:

Поиск фрагмента кода в определениях объектов базы данных

select obj.[object_id],
	   obj.[name],
	   obj.[type_desc],
	   sc.[text]
from sys.syscomments as sc
inner join sys.objects obj on sc.[id]=obj.[object_id]
where sc.[text] like '%code snippet%';

Здесь будет выведен идентификатор, название, описание и полное определение объекта.

Поиск с помощью бесплатной утилиты dbForge Search

Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.

Для вызова этой утилиты в окне SSMS нажмите на кнопку .

Появится следующее окно поиска:

Обратите внимание на верхнюю панель (слева направо):

  1. можно переключать режим поиска (ищем DDL (объекты) или данные)
  2. непосредственно что ищем (какую подстроку)
  3. учитывать ли регистр, искать точное соответствие слову, искать вхождения:

  4. группировать результат по типам объектов — кнопка
  5. выбрать нужные типы объектов для поиска:

  6. также можно задать несколько баз данных для поиска и выбрать экземпляр MS SQL Server

Это все в режиме поиска объектов, т е когда включен DDL:

В режиме поиска данных изменится только выбор типов объектов:

А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:

Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:

Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.

При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.

Также можно переместить навигацию на найденный объект, щелкнув на кнопку :

Можно также сгруппировать найденные объекты по их типу:

Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.

Теперь найдем значение “Ramiro” по всем таблицам:

Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.

Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :

Таким образом мы можем искать нужные объекты и данные в базе данных.

Заключение

Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.

Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.

Источники

  • Search_Script.sql
  • SSMS
  • dbForge Search
  • Документация по Microsoft SQL
  • Бесплатные решения от компании Devart

There are several ways to search and find the tables in the SQL Server database by table name or by column name. Below are few of the methods to find table in the database. I’ve used these methods in SQL Server 2014 and they are working good.

I. Find Table By Table Name Querying sys.tables

The most common and simple method to find and list down the tables in a database based on the name of the table or a phrase is by using this simple select query against the system table sys.tables. If you are a sql expert then this will be the first option you will choose.

SELECT 
	* 
FROM 
	sys.tables 
WHERE 
	name 
LIKE 
	'%product%'

II. Find Table By Table Name Using Filter Settings in Object Explores

Another easiest method to find the tables by the table’s name in SQL Server database is to use the filter settings option in the object explorer in SQL Server Management Studio.

  1. In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
  2. Right Click the Tables folder and select Filter in the right-click menu.
  3. Under filter, select Filter Settings. The filter settings window will pop up.
    MS SQL Table Creation Date Filter
  4. In the filter settings window against the property Name, you can choose one of the Operator from the list (Equals, Contains, Does not contain) and enter the search phrase (or table name) under Value and then click OK.
    MS SQL Find Table By Name
  5. The list of tables under the Tables folder are filtered based on your filter criteria.

III. Find Table From All The Databases By Table Name

There could be a specific scenario to find the table with the same name or phrase of the name from all the databases in the SQL Server. If the SQL Server has less than 10 user databases then it will be easy for you to use one among the above two methods to find the table in the databases separately. But, In case if the SQL Server has hundreds of databases, then searching for the table in each database is difficult and time-consuming. In this scenario you can use the sp_msforeachdb system stored procedure. sp_msforeachdb allows you to execute a command against all the databases in the current SQL Server. You can use this system stored procedure to execute a select command to fetch the list of tables with filter condition from sys.tables against all the databases.

EXEC 
	sys.sp_msforeachdb 
	'SELECT ''?'' DatabaseName, Name FROM [?].sys.Tables WHERE Name LIKE ''%product%'''

MS SQL Find Table

IV. Find Table By Column Name Using SQL Query

Another common scenario in the database world is to find the tables having the specific column or the columns containing a phrase in the name. For such a need to find the tables having the column, you can query the sys.tables system table and joining the sys.columns system table. Here is the simple select query to find the list of tables having a column.

SELECT 
	sys.tables.name AS 'Table Name', 
	sys.tables.object_id AS 'Object ID', 
	sys.columns.name AS 'Column Name'
FROM
	sys.tables INNER JOIN sys.columns 
		ON sys.tables.object_id = sys.columns.object_id
WHERE 
	sys.columns.name LIKE '%ProductID%'
ORDER BY 1;

SQL Server Fild All Tables Having Column

Another method to get the list of tables having the same column is by querying the sys.columns table along with the OBJECT_NAME built-in meta data function instead of using sys.column table. Here is the select command.

SELECT 
	OBJECT_NAME(object_id) AS 'Table Name', 
	object_id, 
	name AS 'Column Name'
FROM 
	sys.columns 
WHERE 
	name LIKE '%ProductID%'

Finnd Tables Having The Same Column

If you know any other method please do add them by commenting below.

Related Articles

  • Get the row count of all the tables In a database.
  • Search For Stored Procedure.
  • Get the list of all the user defined functions in a database.

Reference

  • Stackoverflow

Find a Table on a SQL Server across all Databases

To find a table in the SQL across all databases you can use undocumented stored procedure
sp_MSForEachDB.

sp_MSforeachdb
'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

 

Alternatively, instead of using undocumented stored procedure, you can simply create a query like this:

DECLARE
@SQL NVARCHAR(max)

SET
@SQL = stuff((

            SELECT
'

UNION

SELECT '
+ quotename(NAME,
'''') +
' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name

FROM '
+ quotename(NAME) +
'.sys.tables WHERE NAME LIKE ''%'' + @TableName + ''%'''

            FROM
sys.databases

            ORDER
BY
NAME

            FOR
XML PATH('')

                ,type

            ).value('.',
'nvarchar(max)'), 1, 8,
'')

--PRINT @SQL;

EXECUTE
sp_executeSQL @SQL

    ,N'@TableName varchar(30)'

    ,@TableName =
'items'

Other Languages

  • Encontrando uma Tabela sobre Todos
    os Bancos de Dados SQL Server (pt-BR)

en-US, has code, has code block, has image, has Other Languages, Has Table, Multi Language Wiki Articles, search a table, SQL Server, Translated into Portuguese, T-SQL

Comments

  • 22 Nov 2013 10:16 PM

    Nice script !

  • 7 Apr 2014 1:22 PM

    I’ve changed the alternative query to display the actual table name instead of the search string.  Apparently my ReportServer databases have a different collation so I had to add the collate part as well.

  • 19 Feb 2015 1:46 AM

    It might be good to add where statement to limit the result to databases where state is «online»

               WHERE state = 0

    msdn.microsoft.com/…/ms178534.aspx

  • 16 Oct 2017 3:28 AM

    For using the undocumented SP and returning only results if the table is found you can use the following:

    sp_MSforeachdb ‘if exists (SELECT «?» AS DB, * FROM [?].sys.tables WHERE name like »%MSP_EpmProject%») SELECT «?» AS DB, * FROM [?].sys.tables WHERE name like »%MSP_EpmProject%»’

  • 3 Apr 2020 6:40 PM

    Nice to have this clause

    WHERE user_access_desc =’MULTI_USER’ and state_desc = ‘ONLINE’ AND is_read_only = 0

  • 3 Apr 2020 6:59 PM

    Nice to have this clause

    WHERE user_access_desc =’MULTI_USER’ and state_desc = ‘ONLINE’ AND is_read_only = 0  AND database_id > 4

I use the following to search for strings within the stored procedures of a specific database:

USE DBname
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%xxx%'

Is it easy to amend the above so that it searches Table names in a specific db «DBname» ?

asked Oct 26, 2012 at 11:00

whytheq's user avatar

whytheqwhytheq

34.1k64 gold badges170 silver badges265 bronze badges

1

I’m using this and works fine

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%%'

answered Mar 3, 2014 at 14:03

NeshaSerbia's user avatar

NeshaSerbiaNeshaSerbia

2,2842 gold badges14 silver badges13 bronze badges

5

select name
  from DBname.sys.tables
 where name like '%xxx%'
   and is_ms_shipped = 0; -- << comment out if you really want to see them

answered Oct 26, 2012 at 11:03

RichardTheKiwi's user avatar

RichardTheKiwiRichardTheKiwi

105k26 gold badges196 silver badges262 bronze badges

3

If you want to look in all tables in all Databases server-wide and get output you can make use of the undocumented sp_MSforeachdb procedure:

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%Table_Names%'''

Matthew's user avatar

Matthew

1,6101 gold badge13 silver badges17 bronze badges

answered Dec 30, 2017 at 18:06

ransems's user avatar

ransemsransems

6317 silver badges19 bronze badges

1

You can also use the Filter button to filter tables with a certain string in it.
You can do the same with stored procedures and views.

enter image description here

answered Mar 21, 2019 at 17:04

live-love's user avatar

live-lovelive-love

47.7k22 gold badges234 silver badges201 bronze badges

I am assuming you want to pass the database name as a parameter and not just run:

SELECT  *
FROM    DBName.sys.tables
WHERE   Name LIKE '%XXX%'

If so, you could use dynamic SQL to add the dbname to the query:

DECLARE @DBName NVARCHAR(200) = 'YourDBName',
        @TableName NVARCHAR(200) = 'SomeString';

IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE Name = @DBName)
    BEGIN
        PRINT 'DATABASE NOT FOUND';
        RETURN;
    END;

DECLARE @SQL NVARCHAR(MAX) = '  SELECT  Name
                                FROM    ' + QUOTENAME(@DBName) + '.sys.tables
                                WHERE   Name LIKE ''%'' + @Table + ''%''';

EXECUTE SP_EXECUTESQL @SQL, N'@Table NVARCHAR(200)', @TableName;

answered Oct 26, 2012 at 11:07

GarethD's user avatar

GarethDGarethD

67.7k10 gold badges83 silver badges122 bronze badges

If you prefer case-insensitive searching:

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME ILIKE '%%'

or

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE Lower(TABLE_NAME) LIKE Lower('%%')

answered Nov 29, 2018 at 11:25

Tim Stack's user avatar

Tim StackTim Stack

3,2093 gold badges18 silver badges39 bronze badges

Adding on to @[RichardTheKiwi]’s answer.

Whenever I search for a list of tables, in general I want to select from all of them or delete them. Below is a script that generates those scripts for you.

The generated select script also adds a tableName column so you know what table you’re looking at:

select 'select ''' + name + ''' as TableName, * from ' + name as SelectTable,
'delete from ' + name as DeleteTable
from sys.tables
where name like '%xxxx%'
and is_ms_shipped = 0; 

answered Mar 29, 2016 at 9:07

Rafi's user avatar

RafiRafi

2,4131 gold badge25 silver badges32 bronze badges

you can also use the show command.

show tables like '%tableName%'

answered Feb 8, 2017 at 13:22

Naseeruddin V N's user avatar

1

I want to post a simple solution for every schema you’ve got. If you are using MySQL DB, you can simply get from your schema all the table’s name and add the WHERE-LIKE condition on it. You also could do it with the usual command line as follows:

SHOW TABLES WHERE tables_in_<your_shcema_name> LIKE '%<table_partial_name>%';

where tables_in_<your_shcema_name> returns the column’s name of SHOW TABLES command.

answered Feb 11, 2019 at 11:43

Marcello Marino's user avatar

You can use below :

Select * from sys.tables where name like '%yourtablename%'

Kaveh's user avatar

Kaveh

1,1405 silver badges16 bronze badges

answered Apr 25, 2022 at 14:18

Pritish Kumar Nayak's user avatar

This will working fine….

SELECT * FROM sys.TABLES
WHERE name LIKE ‘%%’

answered Nov 25, 2022 at 9:28

Er.Manoj Singh's user avatar

1

If you simply want the table name you can run:

select object_name(object_id) from sys.columns
where name like '%received_at%'

If you want the Schema Name as well (which in a lot of cases you will, as you’ll have a lot of different schemas, and unless you can remember every table in the database and where it belongs this can be useful) run:

select OBJECT_SCHEMA_NAME(object_id),object_name(object_id) from sys.columns
where name like '%received_at%'

and finally if you want it in a nicer format (although this is where the code (In my opinion) is getting too complicated for easy writing):

select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columns
where name like '%received_at%'

note you can also create a function based on what I have:

CREATE PROCEDURE usp_tablecheck
--Scan through all tables to identify all tables with columns that have the provided string
--Stephen B
@name nvarchar(200)
AS
SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columns
WHERE name LIKE CONCAT('%',@name,'%')
ORDER BY [Table Name] ASC, [Column] ASC
GO

It is worth noting that the concat feature was added in 2012. For 2008r2 and earlier use + to concatenate strings.

I’ve re-formatted the proc a bit since I posted this. It’s a bit more advanced now but looks a lot messier (but it’s in a proc so you’ll never see it) and it’s formatted better.

This version allows you to have it in an administrative database and then search through any database. Change the decleration of @db from 'master' to whichever you want the default database to be (NOTE: using the CONCAT() function will only work with 2012+ unless you change the string concatenation to use the + operators).

CREATE PROCEDURE [dbo].[usp_tablecheck]
    --Scan through all tables to identify all tables in the specified database with columns that have the provided string
    --Stephen B
    @name nvarchar(200)
    ,@db nvarchar(200) = 'master'
AS
    DECLARE @sql nvarchar(4000) = CONCAT('
        SELECT concat(OBJECT_SCHEMA_NAME(col.object_id,DB_ID(''',@db,''')),''.'',object_name(col.object_id,DB_ID(''',@db,'''))) AS [Table Name]
            ,col.name AS [Column] 
        FROM ',@db,'.sys.columns col
        LEFT JOIN ',@db,'.sys.objects ob 
            ON ob.object_id = col.object_id
        WHERE 
            col.name LIKE CONCAT(''%'',''',@name,''',''%'') 
            AND ob.type =''U''
        ORDER BY [Table Name] ASC
            ,[Column] ASC')
    EXECUTE (@sql)
GO

Понравилась статья? Поделить с друзьями:

Не пропустите также:

  • Как в виндовс 10 найти солитер
  • Как найти код магнитолы по серийному номеру
  • Как составить кластер на тему путешествие
  • Как составит график работы на четверых
  • Assassin s creed valhalla данные повреждены как исправить

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии