How Did They Make That???

often you may run across a stored procedure, view, or function that peeks your curiosity and you REALLY want to see how it works. This is even true of system objects. There are several ways of provinding the
text to these objects using T-SQL. The first is to query the associated catalog views. For example to view the defintion of all objects in a database you can use the sys.sql_modules and sys.objects catalog views:

SELECT name,
      
o.TYPE,
      
sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.OBJECT_ID = o.OBJECT_ID
ORDER BY o.TYPE;

To find the text of system defined objects you can replace the sys.objects catalog view with the sys.all_objects catalog view

SELECT o.name,
      
m.definition
FROM sys.system_sql_modules m
JOIN sys.all_objects o
ON m.OBJECT_ID = o.OBJECT_ID
ORDER BY o.name;
GO

Another way to find the definition of a specific objects is to use hte system function OBJECT_DEFINITION(). This works on both system defnied and user defined objects:

SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.tables’));
GO

And one last method is to use the system stored procedure sp_helptext which also works for both system and user defined objects:


sp_helptext ‘sys.tables’;
GO

So which one is best, obviously the catalog views provide the ability to return all defined objects while the OBJECT_DEFINITION and sp_help_text are for single objects. Also none of these methods work for encrypted
or CLR objects.

Leave a Reply


Hit Counter provided by Sign Holders