TSQL Queries Search for SQL Objects

Frequently used SQL Queries

 
There are many SQL Queries which are used frequently as a part of our SDLC, I am trying to consolidate all and keep at one place. Hope this helps!


Search for a tablename in all SQL Objects:
There are different ways of searching for a text in all SQL Objects, below are few methods:

SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SEARCHSTRING%'

DECLARE @Search varchar(255)
SET @Search='SearchString'
SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%'+@Search+'%' ORDER BY 2,1


Another simple way of doing this,
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%SearchString%'


Get all Tables across all databases in a Server:

There are few instances where we want to list all the tables in all databases in a database server,

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
INSERT INTO @AllTables (CompleteTableName) EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id'
SET NOCOUNT OFF

--Get Result
SELECT * FROM @AllTables ORDER BY 1