Search of table names

Question

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" ?

**See Also**: [How do I get list of all tables in a database using TSQL?](https://stackoverflow.com/q/175415/1366033) — KyleMit, Jan 20 '22 at 17:23

score 204 · Accepted Answer · answered Mar 03 '14 at 14:03

204

I'm using this and works fine

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

answered Mar 03 '14 at 14:03

NeshaSerbia

2,324
2
14
13

4

Includes views too :-) – François Breton Nov 02 '17 at 15:46
Works for views and tables. – Rajkumar M Feb 03 '21 at 08:59
1

use SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE '%%' for stored procedure. – Rajkumar M Feb 03 '21 at 09:22
1

I suggest to use `ILIKE` instead of `LIKE`, to make the query case insensitive – Tms91 Dec 13 '21 at 11:01
`LIKE` is case-insensitive in SQL Server – InvalidBrainException Jun 09 '22 at 10:07

RichardTheKiwi · Answer 2 · 2012-10-26T11:13:40.390

92

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

edited Oct 26 '12 at 11:13

answered Oct 26 '12 at 11:03

RichardTheKiwi

105,798
26
196
262

commenting out the line `and is_ms_shipped = 0;` doesn't seem to do much – whytheq Oct 26 '12 at 12:42
2

The only thing it normally hides are the database diagram support tables, or is there only one. It's more useful when looking for functions/procedures. – RichardTheKiwi Oct 26 '12 at 13:23
Why not add schema names? select t.schema_id, s.name, t.Name from DBNAME.sys.tables as t inner join DBNAME.sys.schemas as s on t.schema_id = s.schema_id where t.name like '%yourtabletosearch%' and is_ms_shipped = 0 – Orsinus Dec 15 '16 at 07:27

score 15 · Answer 3 · edited Feb 19 '18 at 05:47

15

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%'''

edited Feb 19 '18 at 05:47

Matthew

1,630
1
14
19

answered Dec 30 '17 at 18:06

ransems

641
7
19

3

This answer is the only useful one if you don't know which database a table might be in – Matthew Feb 19 '18 at 00:44

score 12 · Answer 4 · answered Mar 21 '19 at 17:04

12

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.

answered Mar 21 '19 at 17:04

live-love

48,840
22
240
204

score 7 · Answer 5 · answered Oct 26 '12 at 11:07

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;

Tim Stack · Answer 6 · 2022-04-04T11:31:52.947

7

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('%%')

edited Apr 04 '22 at 11:31

answered Nov 29 '18 at 11:25

Tim Stack

3,209
3
18
39

score 3 · Answer 7 · answered Mar 29 '16 at 09:07

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;

score 2 · Answer 8 · answered Feb 08 '17 at 13:22

2

you can also use the show command.

show tables like '%tableName%'

answered Feb 08 '17 at 13:22

Naseeruddin V N

597
5
17

8

This is MySQL syntax on a question tagged sql-server – Mike Guthrie Sep 08 '17 at 13:34

Marcello Marino · Answer 9 · 2019-02-11T16:32:53.870

2

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.

edited Feb 11 '19 at 16:32

answered Feb 11 '19 at 11:43

Marcello Marino

150
6

score 2 · Answer 10 · edited Apr 27 '22 at 08:05

2

You can use below :

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

edited Apr 27 '22 at 08:05

Kaveh

1,158
6
16

answered Apr 25 '22 at 14:18

Pritish Kumar Nayak

21
1

score 1 · Answer 11 · answered Nov 25 '22 at 09:28

1

This will working fine....

SELECT * FROM sys.TABLES WHERE name LIKE '%%'

answered Nov 25 '22 at 09:28

Er.Manoj Singh

31
2

Welcome to Stackoverflow. This question is asked more than 10 years ago and it has an accepted answer. Please add some details about the reason you are adding a new answer. – MD Zand Nov 30 '22 at 13:09

Search of table names

11 Answers11

Linked