Sep 26, 2013

Script to get the list of all natively compiled stored procedures in sql server 2014

We can get the list of all natively complied stored procedures in the current database by following sql query:

SELECT
     SCHEMA_NAME([schema_id]) AS SchemaName,
     name AS ProcedureName
FROM sys.procedures
WHERE OBJECTPROPERTY([object_id],'Isschemabound') = 1

Sample Output:

SchemaName
ProcedureName
dbo
uspGetEmployee
dbo
uspAddSkills
dbo
uspGetType


As we know a stored procedure can be schema bound if and only if it is natively complied stored procedures. So we are using this feature to get the list.  Here sys.procedures is system view. 

No comments:

Post a Comment