How to list all objects of a particular database in SQL Server
59,223
Solution 1
Which database are you running this in? When I run it in a particular database, I don't get anything outside that database.
Solution 2
List all procs, views, tables, functions in Sql Server:
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 '%[^a-z]employeeid[^a-z]%'
order by type_desc, object_name
The comment is if you want to search for a particular (whole) word.
Solution 3
This is what I use.
SELECT o.type_desc AS Object_Type
, s.name AS Schema_Name
, o.name AS Object_Name
FROM sys.objects o
JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type NOT IN ('S' --SYSTEM_TABLE
,'PK' --PRIMARY_KEY_CONSTRAINT
,'D' --DEFAULT_CONSTRAINT
,'C' --CHECK_CONSTRAINT
,'F' --FOREIGN_KEY_CONSTRAINT
,'IT' --INTERNAL_TABLE
,'SQ' --SERVICE_QUEUE
,'TR' --SQL_TRIGGER
,'UQ' --UNIQUE_CONSTRAINT
)
ORDER BY Object_Type
, SCHEMA_NAME
, Object_Name
Author by
titanium
Updated on March 03, 2021Comments
-
titanium about 3 years
I would like to list all objects of a particular database in SQL Server. I created a query as shown below:
select name, type_desc from sys.objects WHERE type in ( 'C', 'D', 'F', 'L', 'P', 'PK', 'RF', 'TR', 'UQ', 'V', 'X' ) union select name, type_desc from sys.indexes order by name
However, this query list all objects of ALL databases rather than a particular database.
My question is: Is there a way to query all objects of just a particular database?
-
titanium almost 15 yearsThanks. My mistake. I can get objects of a particular database.
-
Chad Baldwin over 4 years*within a database. This won't search all databases, just the scope of the current database.
-
Fandango68 almost 4 years@chadwin that's actually what he wanted