{"id":2901,"date":"2012-10-24T16:01:21","date_gmt":"2012-10-24T16:01:21","guid":{"rendered":"http:\/\/craftydba.com\/?p=2901"},"modified":"2012-11-13T16:29:45","modified_gmt":"2012-11-13T16:29:45","slug":"database-files-objects-part-4","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2901","title":{"rendered":"Database Files & Objects – Part 4"},"content":{"rendered":"

\"\"<\/a><\/p>\n

Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. <\/p>\n

We will be investigating how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on functions, stored procedures, triggers and views in this article.<\/p>\n

Let’s use the USE statement to select the sample database.<\/p>\n

-- Which database to use.\r\nUSE [MATH]\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
VIEWS – MSDN QUOTE:
\n“A view can be thought of as either a virtual table or a stored query.  The data accessible through a view is not stored in the database as a distinct object.  What is stored in the database is a SELECT statement.  The result set of the SELECT statement forms the virtual table returned by the view.  A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.”<\/span> <\/p>\n
If we want to get a simple list of all the views in a database, one can query the sys.tables catalog view.  <\/p>\n
-- My one view (V)\r\nselect * from sys.views\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
STORED PROCEDURES – MSDN QUOTE:
\n“A stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework Common Runtime Language (CLR) method.  Procedures resemble constructs in other programming languages because they can:  accept input parameters and return multiple values in the form of output parameters to the calling program; contain programming statements that perform operations in the database include calling other procedures; and return a status value to a calling program to indicate success or failure.”<\/span><\/p>\n
If we want to get a simple list of all the stored procedures in a database, one can query the sys.procedures catalog view.  <\/p>\n
-- My two stored procs (SP) \r\nselect * from sys.procedures\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
DML TRIGGERS – MSDN QUOTE:
\n“DML triggers are a special type of stored procedure that automatically takes effect when a data manipulation language event takes place that affects the table or view defined in the trigger.  DML events include INSERT, UPDATE, or DELETE statements.  DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.  The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger.<\/span><\/p>\n
If we want to get a simple list of all the DML triggers in a database, one can query the sys.triggers catalog view.  <\/p>\n
-- My one trigger (TR)\r\nselect * from sys.triggers\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
DDL TRIGGERS – MSDN QUOTE:
\n“You can use DDL triggers to audit database or server operations that create, modify, or delete database objects or ensure that DDL statements enforce your business rules before they are run.  DDL triggers fire in response to a variety of Data Definition Language (DDL) events.  These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.  Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.”
\n<\/span><\/p>\n
If we want to get a simple list of all the DDL triggers in a database, one can query the sys.server_triggers catalog view.  Since we have no objects of this type, the result set is empty.  <\/p>\n
-- My one trigger (TR)\r\nselect * from sys.server_triggers\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
FUNCTIONS – MSDN QUOTE:
\n“A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value.  The return value can either be a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in these ways:
\n<\/span>
\nIn Transact-SQL statements such as SELECT
\nIn applications calling the function
\nIn the definition of another user-defined function
\nTo parameterize a view or improve the functionality of an indexed view
\nTo define a column in a table
\nTo define a CHECK constraint on a column
\nTo replace a stored procedure”
\n<\/span><\/p>\n
If we want to get a simple list of all functions in a database, we have to query the catch all sys.objects catalog view.  There is no sub-set view like the prior examples.<\/p>\n
-- My one function\r\nselect * from sys.objects where type_desc like '%fun%'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a>
\nThere is an older system stored procedure that we can used to list basic information on stored procedures and functions.  By suppling the object name as a parameter, the sp_stored_procedures will return some very basic information.<\/p>\n
-- Older system stored proc - just one procedure\r\nsp_stored_procedures @sp_name = 'SP_IS_PRIME'\r\ngo\r\n\r\n\"\"<\/a>\r\n\r\n-- Older system stored proc - just one function\r\nsp_stored_procedures @sp_name = 'FN_GET_PRIMES_BY_RANGE'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
There are times as a SQL developer in which you want to know the parameters that are passed to a given stored procedure or function.  We can query the sys.parameters catalog view and the filter by all user objects from the sys.objects tables.<\/p>\n
-- Parameters to SP & FN\r\nselect o.name, p.* from sys.parameters p join sys.objects o\r\non p.object_id = o.object_id where is_ms_shipped = 0\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Another way to do this same task is to execute the sp_sproc_columns stored procedure with the correct parameter.  If we use object owner, we can get both procedures and functions.  If we use object name, we will get just one listing.<\/p>\n
-- Older system stored proc - show all parameters to SP & FN\r\nsp_sproc_columns @procedure_owner = 'dbo'\r\ngo\r\n\r\n\"\"<\/a>\r\n\r\n-- Older system stored proc - show all parameters to one\r\nsp_sproc_columns @procedure_name = 'SP_STORE_PRIMES'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
There are many times that we want to see the code behind these objects.  We can query the sys.sql_modules catalog view to get the information and filter by all object type in views, store procedures, triggers or views.<\/p>\n
-- TSQL code of V, SP, FN & TR\r\nSELECT o.name, o.type, m.definition\r\nFROM sys.sql_modules AS m\r\nJOIN sys.objects AS o ON m.object_id = o.object_id \r\nwhere o.type IN ('FN', 'IF', 'TF', 'SF', 'ST', 'P', 'V', 'TR');\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Another way to do this same task is to execute the sp_helptext stored procedure with the correct parameter.  If we use object name, we will get just one listing.<\/p>\n
-- Older system stored proc - code by object name\r\nsp_helptext @objname = 'TRG_TBL_PRIMES';\r\ngo\r\n\r\nsp_helptext @objname = 'SP_IS_PRIME';\r\ngo\r\n\r\nsp_helptext @objname = 'SP_STORE_PRIMES';\r\ngo\r\n\r\nsp_helptext @objname = 'VW_ONE_HUNDRED_K_PRIMES';\r\ngo\r\n\r\nsp_helptext @objname = 'FN_GET_PRIMES_BY_RANGE';\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
large screen shot #1<\/a><\/p>\n
Last but not least, we can query the sys.objects catalog view to return all user defined objects.<\/p>\n
-- All my objects\r\nselect * from sys.objects where is_ms_shipped = 0\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
To recap this discussion, system stored procedures can be used grab information about database views, stored procedures, triggers and functions.  Use system catalog views instead to get detailed information on both.  In the future, I am sure I will be writing TSQL scripts using these commands to do maintenance tasks where third party vendors come up short.  <\/p>\n
Next time, I will be talking about using system stored procedures and catalog views to look at the following database objects used in file partitioning for large data sets.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be investigating how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on functions, stored procedures, triggers and views in this article. Let’s use the USE statement to select the sample database. — Which database to use. USE [MATH]…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,12,591,15,319,598,596,28,592,325,597,594,401,599,595,593,568,567,390,29,590],"class_list":["post-2901","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-database-developer","tag-free-code","tag-functions","tag-john-f-miner-iii","tag-sp_helptext","tag-sp_sproc_columns","tag-sp_stored_procedures","tag-sql-server","tag-stored-procedures","tag-sys-objects","tag-sys-parameters","tag-sys-procedures","tag-sys-server_triggers","tag-sys-sql_modules","tag-sys-triggers","tag-sys-views","tag-system-catalog-views","tag-system-stored-procedures","tag-triggers","tag-tsql","tag-views"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2901","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2901"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2901\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2901"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2901"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2901"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}