How to get the objects for specific database?

Question

I have a procedure which takes as an argument database name and I need using that database name to get all objects refer to it including tables,views,triggers which dictionary would be helpful to find those information ?

NOTE :I Tryed to use SELECT * FROM sys.obj but that gives me the whole objects in all databases . how can i restrict them to specifc database

You will have to use dynamic sql. And you will need to include the database name as part of your dynamic sql. "Select * from " + @DBName + ".sys.objects..." — Sean Lange, Dec 19 '16 at 22:42
When modified this answer should get you going: http://stackoverflow.com/a/18462734/2095534 — Kamil Gosciminski, Dec 19 '16 at 22:43

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

I have run the same statement which you mentioned in NOTE . Then I am getting only the Objects which I was connected to. So simply add USE to get connected to particular db. And hit the sys.objects table.

So your SP will be like

GO
CREATE PROCEDURE PROC_GETALLOBJ
(
@DB_NAME VARCHAR(20)
)
AS 
BEGIN
    DECLARE @QRY VARCHAR(MAX);

    SELECT @QRY = '
    USE ' +@DB_NAME+'

    SELECT * FROM SYS.objects WHERE [TYPE] NOT IN (''SQ'',''S'',''IT'')'
    
    EXEC (@QRY)
    
END
GO

I have removed rows which are of Type SQ,S,IT with NOT IN. Because you might not need them.

Please find the list of types with Description.

TR --TRIGGER

SQ --SERVICE_QUEUE

FN --SQL_SCALAR_FUNCTION

S --SYSTEM_TABLE

D --DEFAULT_CONSTRAINT

IT --INTERNAL_TABLE

F --FOREIGN_KEY_CONSTRAINT

PK --PRIMARY_KEY_CONSTRAINT

P --SQL_STORED_PROCEDURE

U --USER_TABLE

TF --SQL_TABLE_VALUED_FUNCTION

UQ --UNIQUE_CONSTRAINT

V --VIEW

How to get the objects for specific database?

1 Answers1