Search of table names

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

10 Answers

I'm using this and works fine

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

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

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

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.

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; 

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

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; 

you can also use the show command.

show tables like '%tableName%' 

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.

You can use below :

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