Wednesday, April 20, 2011

What is a SQL Server module?

I just came across the system view sys.sql_modules today. What is a module versus a DB object? The view returns, most prominently, a column containing the definition text, as returned by sys.syscomments.

From stackoverflow
  • A modules are functions, procedures, queues, and triggers. These Modules call Objects. That's what I understood from this article which describes "EXECUTE AS":

    EXECUTE AS

    ProfK : It was that page that led me to ask my question in the first place. It tells me that there are various modules in a DB. I've always known that there are various objects in a DB. Whence 'modules'?
    NTulip : i redefined the answer just to make it clear.
  • It's the blocks of T-SQL statements that make up a stored procedure, a stored function, a trigger or a view definition.

    From "Books Online" in the "CREATE PROCEDURE" section:

    Getting Information About Stored Procedures To display the definition of a Transact-SQL stored procedure, use the sys.sql_modules catalog view in the database in which the procedure exists.

    In sys.sql_modules, you'll find the actual T-SQL code.

    Marc

0 comments:

Post a Comment