We usually use system tables to get system level information , there is one table sys.objects which is having information about all system objects including system/user tables, views, functions, procedures etc. Below is the example
select DB_Name()
as DatabaseName, o.name as ObjectName,
schema_name(schema_id)
as SchemaName,
o.type
Object_type, o.type_desc, *
from sys.objects
o
order by object_id
desc
Another system table is sys.modules which
keeps detail information about code of a module (stored procedure or view)
below is the example
select s.definition as
Code, * from sys.sql_modules s
we can combine both system tables
with object_id column to see object details with sql-code,
Select DB_Name() as
DatabaseName, o.name as ObjectName, schema_name(schema_id)
as SchemaName, o.type Object_type,
o.type_desc , s.definition as Code
from sys.objects
o JOIN sys.sql_modules
s ON s.object_id = o.object_id
where is_ms_shipped=0
Above code will show the objects and
their detail of current database, if we want to search some object or code
within whole server then we will utilize same code in cursor to navigate
through whole server(all database).
In
our code we will create a temp table to hold all the information, and then
query that specific object(temp table) to find out whatever is our requirement.
/***************
*******************ACTUAL CODE
********************************************
STEP 1 is
to create ##object table and pass all objects information into the table
********************/
IF
OBJECT_ID('tempdb..##Objects') IS NOT NULL
DROP TABLE ##Objects
CREATE TABLE
##Objects
(
DatabaseName Varchar(500),
ObjectName varchar(500),
SchemaName varchar(100),
ObjectType varchar(10),
ObjectDesc varchar(100),
sqlcode varchar(max)
)
DECLARE @DatabaseName
as varchar(500)
DECLARE DBcursor
CURSOR FOR
SELECT NAME from sys.databases
OPEN DBcursor
FETCH NEXT FROM DBcursor
into @DatabaseName
WHILE @@FETCH_STATUS =
0
BEGIN
DECLARE
@DBName as NVARCHAR(500);
SET @DBName
= QUOTENAME(N'' + @DatabaseName + '');
EXEC (N'USE
' + @DBName + N';
EXEC(''Insert into ##Objects
Select
DB_Name() as DatabaseName, o.name as bjectName,
schema_name(schema_id)
as SchemaName,
o.type
Object_type, o.type_desc , s.definition as Code
from
sys.objects o
JOIN
sys.sql_modules s
ON
s.object_id = o.object_id
where
is_ms_shipped=0''
);'
);
FETCH NEXT FROM DBcursor
into @DatabaseName
END
CLOSE dbcursor
DEALLOCATE dbcursor
/********** STEP 2
Run
below query and change arguments as per your requirement
***************************/
/**** To search any
object ******/
select * from
##objects
where objectName like
'%sp_03d_EFVEH_SSS%'
and ObjectType = 'P'
/******* To search for
a particular code within stored procedure or view or function ***/
select * from
##objects
where ObjectType =
'P'
and sqlcode like
'%INSERT INTO Proc_GIS.dbo.GisUnqRec_FirTmp%'
Other
Developer related blogs
table data comparison
sqlcmd export data to txt
sql analytical functions
script to generate foreign keys
convert values into rows
