CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
overwhelmedaf
overwhelmedaf

Reputation: 63

How do I include a stored procedures schema name in a select statement

I would like to select all of the stored procedures in my database preceded by their schema name.

For example I would like my output columns to show dbo.stored_procedure instead of just stored_procedure.

So what I'm looking for is something like 

select SCHEMA_NAME + '.' + name
from sys.procedures
order by name

But this won't work because of schema_name being an invalid column name

Upvotes: 0

Views: 812

Answers (5)

Ilyes
Ilyes

Reputation: 14928

You can use SCHEMA_NAME() function to get the schema name as

SELECT CONCAT(QUOTENAME(SCHEMA_Name(schema_id)), N'.', QUOTENAME(Name)) Result
FROM Sys.Procedures
ORDER BY Name;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use INFORMATION_SHEMA.ROUTINES:

select ROUTINE_SCHEMA + '.' + ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE';

You can use either SPECIFIC_ or ROUTINE_. However, the SPECIFIC_ are for overloaded procedures (i.e. two procedures with the same name but that take different arguments). SQL Server doesn't support this. If it did, though, you would want the generic name.

Upvotes: 1

Jon
Jon

Reputation: 3255

select SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME
from INFORMATION_SCHEMA.ROUTINES

Upvotes: 0

Jamiec
Jamiec

Reputation: 136114

sys.procedures has a field schema_id which is a foreignkey to sys.schemas. So you simply need to join to it

select s.name + '.' + p.name
from sys.procedures p
inner join sys.schemas s
    on p.schema_id = s.schema_id
order by p.name

Upvotes: 1

Thom A
Thom A

Reputation: 95557

Schema name is in a different object if you're using the sys objects:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(p.name)
FROM sys.procedures p
     JOIN sys.schemas s ON p.schema_id = s.schema_id
ORDER BY p.name;

Upvotes: 4

Related Questions