I use the following to search for strings in my stored procedures:
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" ?
This question is related to
sql
sql-server
sql-server-2008-r2
I know this is an old thread, but if you prefer case-insensitive searching:
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE Lower(TABLE_NAME) LIKE Lower('%%')
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%'''
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;
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;
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.
select name
from DBname.sys.tables
where name like '%xxx%'
and is_ms_shipped = 0; -- << comment out if you really want to see them
Source: Stackoverflow.com