Query to list all stored procedures

Question

What query can return the names of all the stored procedures in a SQL Server database  If the query could exclude system stored procedures  that would be even more helpful

User · Answer

Try this codeplex link  this utility help to localize all stored procedure from sql database   https   exportmssqlproc codeplex com

User · Answer

select   from DatabaseName INFORMATION SCHEMA ROUTINES where routine type    PROCEDURE   select   from DatabaseName INFORMATION SCHEMA ROUTINES where routine type   procedure  and left ROUTINE NAME 3  not in  sp     xp     ms         SELECT name  type   FROM dbo sysobjects  WHERE  type    P

User · Answer

I wrote this simple tsql to list the text of all stored procedures   Be sure to substitute your database name in  field   use  lt  lt  database name  gt  gt  go  declare  aQuery nvarchar 1024   declare  spName nvarchar 64   declare allSP cursor for select p name  from sys procedures p where p type desc    SQL STORED PROCEDURE  order by p name  open allSP  fetch next from allSP into  spName  while    FETCH STATUS   0  begin     set  aQuery    sp helptext  Extract      spName            exec sp executesql  aQuery      fetch next from allSP  end  close allSP  deallocate allSP

User · Answer

From my understanding the  preferred  method is to use the information schema tables   select      from information schema routines   where routine type    PROCEDURE

User · Answer

If you are using SQL Server 2005 the following will work   select     from sys procedures  where is ms shipped   0

User · Answer

Select All Stored Procedures and Views  select name type type desc from sys objects where type in   V   P   order by name type

User · Answer

Unfortunately INFORMATION SCHEMA doesn t contain info about the system procs   SELECT     FROM sys objects  WHERE objectproperty object id  N IsMSShipped     0    AND objectproperty object id  N IsProcedure     1

User · Answer

You can use one of the below queries to find the list of Stored Procedures in one database     Query1        SELECT                FROM sys procedures    Query2        SELECT                 FROM information schema routines      WHERE ROUTINE TYPE    PROCEDURE     If you want to find the list of all SPs in all Databases you can use the below query        CREATE TABLE  ListOfSPs                DBName varchar 100             OBJECT ID  INT          SPName varchar 100             EXEC sp msforeachdb  USE      INSERT INTO  ListOfSPs Select        Object Id  Name FROM sys procedures       SELECT                 FROM  ListOfSPs

User · Answer

This can also help to list procedure except the system procedures   select   from sys all objects where type  p  and is ms shipped 0

User · Answer

This is gonna show all the stored procedures and the code   select sch name As  Schema   obj name AS  Stored Procedure   code definition AS  Code  from sys objects as obj     join sys sql modules as code on code object id   obj object id     join sys schemas as sch on sch schema id   obj schema id     where obj type    P

User · Answer

This will give just the names of the stored procedures   select specific name from information schema routines where routine type    PROCEDURE

User · Answer

the best way to get objects is use sys sql modules  you can find every thing that you want from this table and join this table with other table to get more information by object id  SELECT o  object id o name AS name o type desc m definition schemas name scheamaName FROM sys sql modules        m      INNER JOIN sys objects  o ON m object id o OBJECT ID     INNER JOIN sys schemas ON schemas schema id   o schema id     WHERE  TYPE   p

User · Answer

If you are using SQL Server 2005 the following will work   select     from sys procedures  where is ms shipped   0

User · Answer

This is gonna show all the stored procedures and the code   select sch name As  Schema   obj name AS  Stored Procedure   code definition AS  Code  from sys objects as obj     join sys sql modules as code on code object id   obj object id     join sys schemas as sch on sch schema id   obj schema id     where obj type    P

User · Answer

Unfortunately INFORMATION SCHEMA doesn t contain info about the system procs   SELECT     FROM sys objects  WHERE objectproperty object id  N IsMSShipped     0    AND objectproperty object id  N IsProcedure     1

User · Answer

SELECT name          type   FROM dbo sysobjects  WHERE  type    P

User · Answer

From my understanding the  preferred  method is to use the information schema tables   select      from information schema routines   where routine type    PROCEDURE

User · Answer

As Mike stated  the best way is to use information schema   As long as you re not in the master database  system stored procedures won t be returned   SELECT      FROM DatabaseName INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE    If for some reason you had non-system stored procedures in the master database  you could use the query  this will filter out MOST system stored procedures    SELECT      FROM  master  INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE      AND LEFT ROUTINE NAME  3  NOT IN   sp     xp     ms

User · Answer

Unfortunately INFORMATION SCHEMA doesn t contain info about the system procs   SELECT     FROM sys objects  WHERE objectproperty object id  N IsMSShipped     0    AND objectproperty object id  N IsProcedure     1

User · Answer

This  list all things that you want  In Sql Server 2005  2008  2012    Use  YourDataBase   EXEC sp tables  table type     PROCEDURE    EXEC sp tables  table type     TABLE   EXEC sp tables  table type     VIEW      OR  SELECT   FROM information schema tables SELECT   FROM information schema VIEWS

User · Answer

USE DBNAME  select ROUTINE NAME from information schema routines  where routine type    PROCEDURE    GO    This will work on mssql

User · Answer

SELECT name          type   FROM dbo sysobjects  WHERE  type    P

User · Answer

exec sp stored procedures  Docs Microsoft com Easy to remember

User · Answer

I wrote this simple tsql to list the text of all stored procedures   Be sure to substitute your database name in  field   use  lt  lt  database name  gt  gt  go  declare  aQuery nvarchar 1024   declare  spName nvarchar 64   declare allSP cursor for select p name  from sys procedures p where p type desc    SQL STORED PROCEDURE  order by p name  open allSP  fetch next from allSP into  spName  while    FETCH STATUS   0  begin     set  aQuery    sp helptext  Extract      spName            exec sp executesql  aQuery      fetch next from allSP  end  close allSP  deallocate allSP

User · Answer

USE DBNAME  select ROUTINE NAME from information schema routines  where routine type    PROCEDURE    GO    This will work on mssql

User · Answer

select   from DatabaseName INFORMATION SCHEMA ROUTINES where routine type    PROCEDURE   select   from DatabaseName INFORMATION SCHEMA ROUTINES where routine type   procedure  and left ROUTINE NAME 3  not in  sp     xp     ms         SELECT name  type   FROM dbo sysobjects  WHERE  type    P

User · Answer

I ve tweaked LostCajun s excellent post above to exclude system stored procedures  I also removed  Extract   from the code because I couldn t figure out what it s for and it gave me errors  The  fetch next  statement inside the loop also needed an  into  clause   use  lt  lt databasename gt  gt  go  declare  aQuery nvarchar 1024   declare  spName nvarchar 64   declare allSP cursor for     select p name       from sys procedures p      where p type desc    SQL STORED PROCEDURE       and LEFT p name 3  NOT IN   sp    xp    ms        order by p name  open allSP  fetch next from allSP into  spName  while    FETCH STATUS   0  begin     set  aQuery    sp helptext       spName            exec sp executesql  aQuery      fetch next from allSP into  spName  end  close allSP  deallocate allSP

User · Answer

the best way to get objects is use sys sql modules  you can find every thing that you want from this table and join this table with other table to get more information by object id  SELECT o  object id o name AS name o type desc m definition schemas name scheamaName FROM sys sql modules        m      INNER JOIN sys objects  o ON m object id o OBJECT ID     INNER JOIN sys schemas ON schemas schema id   o schema id     WHERE  TYPE   p

User · Answer

From my understanding the  preferred  method is to use the information schema tables   select      from information schema routines   where routine type    PROCEDURE

User · Answer

As Mike stated  the best way is to use information schema   As long as you re not in the master database  system stored procedures won t be returned   SELECT      FROM DatabaseName INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE    If for some reason you had non-system stored procedures in the master database  you could use the query  this will filter out MOST system stored procedures    SELECT      FROM  master  INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE      AND LEFT ROUTINE NAME  3  NOT IN   sp     xp     ms

User · Answer

This will give just the names of the stored procedures   select specific name from information schema routines where routine type    PROCEDURE

User · Answer

From my understanding the  preferred  method is to use the information schema tables   select      from information schema routines   where routine type    PROCEDURE

User · Answer

This  list all things that you want  In Sql Server 2005  2008  2012    Use  YourDataBase   EXEC sp tables  table type     PROCEDURE    EXEC sp tables  table type     TABLE   EXEC sp tables  table type     VIEW      OR  SELECT   FROM information schema tables SELECT   FROM information schema VIEWS

User · Answer

You can try this query to get stored procedures and functions    SELECT name  type FROM dbo sysobjects WHERE type IN        P   -- stored procedures      FN   -- scalar functions       IF   -- inline table-valued functions      TF  -- table-valued functions   ORDER BY type  name

User · Answer

If you are using SQL Server 2005 the following will work   select     from sys procedures  where is ms shipped   0

User · Answer

SELECT name          type   FROM dbo sysobjects  WHERE  type    P

User · Answer

Try this codeplex link  this utility help to localize all stored procedure from sql database   https   exportmssqlproc codeplex com

User · Answer

SELECT name          type   FROM dbo sysobjects  WHERE  type    P

User · Answer

This will returned all sp name   Select    FROM sys procedures where  type     P        AND is ms shipped   0       AND  name  not like  sp    diagram

User · Answer

This will returned all sp name   Select    FROM sys procedures where  type     P        AND is ms shipped   0       AND  name  not like  sp    diagram

User · Answer

Unfortunately INFORMATION SCHEMA doesn t contain info about the system procs   SELECT     FROM sys objects  WHERE objectproperty object id  N IsMSShipped     0    AND objectproperty object id  N IsProcedure     1

User · Answer

The following will Return All Procedures in selected database  SELECT   FROM sys procedures

User · Answer

Select list of stored procedure in SQL server  Refer here for more  https   coderrooms blogspot com 2017 06 select-list-of-stored-procedure-in-sql html

User · Answer

As Mike stated  the best way is to use information schema   As long as you re not in the master database  system stored procedures won t be returned   SELECT      FROM DatabaseName INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE    If for some reason you had non-system stored procedures in the master database  you could use the query  this will filter out MOST system stored procedures    SELECT      FROM  master  INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE      AND LEFT ROUTINE NAME  3  NOT IN   sp     xp     ms

User · Answer

The following will Return All Procedures in selected database  SELECT   FROM sys procedures

User · Answer

I ve tweaked LostCajun s excellent post above to exclude system stored procedures  I also removed  Extract   from the code because I couldn t figure out what it s for and it gave me errors  The  fetch next  statement inside the loop also needed an  into  clause   use  lt  lt databasename gt  gt  go  declare  aQuery nvarchar 1024   declare  spName nvarchar 64   declare allSP cursor for     select p name       from sys procedures p      where p type desc    SQL STORED PROCEDURE       and LEFT p name 3  NOT IN   sp    xp    ms        order by p name  open allSP  fetch next from allSP into  spName  while    FETCH STATUS   0  begin     set  aQuery    sp helptext       spName            exec sp executesql  aQuery      fetch next from allSP into  spName  end  close allSP  deallocate allSP

User · Answer

select       from dbo sysobjects  where xtype    P     and status  gt  0

User · Answer

Select list of stored procedure in SQL server  Refer here for more  https   coderrooms blogspot com 2017 06 select-list-of-stored-procedure-in-sql html

User · Answer

select       from dbo sysobjects  where xtype    P     and status  gt  0

User · Answer

Just the names   SELECT SPECIFIC NAME   FROM YOUR DB NAME information schema routines   WHERE routine type    PROCEDURE

User · Answer

select       from dbo sysobjects  where xtype    P     and status  gt  0

User · Answer

You can use one of the below queries to find the list of Stored Procedures in one database     Query1        SELECT                FROM sys procedures    Query2        SELECT                 FROM information schema routines      WHERE ROUTINE TYPE    PROCEDURE     If you want to find the list of all SPs in all Databases you can use the below query        CREATE TABLE  ListOfSPs                DBName varchar 100             OBJECT ID  INT          SPName varchar 100             EXEC sp msforeachdb  USE      INSERT INTO  ListOfSPs Select        Object Id  Name FROM sys procedures       SELECT                 FROM  ListOfSPs

User · Answer

If you are using SQL Server 2005 the following will work   select     from sys procedures  where is ms shipped   0

User · Answer

select       from dbo sysobjects  where xtype    P     and status  gt  0

User · Answer

Select All Stored Procedures and Views  select name type type desc from sys objects where type in   V   P   order by name type

User · Answer

You can try this query to get stored procedures and functions    SELECT name  type FROM dbo sysobjects WHERE type IN        P   -- stored procedures      FN   -- scalar functions       IF   -- inline table-valued functions      TF  -- table-valued functions   ORDER BY type  name

User · Answer

As Mike stated  the best way is to use information schema   As long as you re not in the master database  system stored procedures won t be returned   SELECT      FROM DatabaseName INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE    If for some reason you had non-system stored procedures in the master database  you could use the query  this will filter out MOST system stored procedures    SELECT      FROM  master  INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE      AND LEFT ROUTINE NAME  3  NOT IN   sp     xp     ms

User · Answer

exec sp stored procedures  Docs Microsoft com Easy to remember

User · Answer

This can also help to list procedure except the system procedures   select   from sys all objects where type  p  and is ms shipped 0

User · Answer

Just the names   SELECT SPECIFIC NAME   FROM YOUR DB NAME information schema routines   WHERE routine type    PROCEDURE

[sql-server] Query to list all stored procedures

Examples related to sql-server

Examples related to tsql