{"id":2824,"date":"2012-10-19T14:39:08","date_gmt":"2012-10-19T14:39:08","guid":{"rendered":"http:\/\/craftydba.com\/?p=2824"},"modified":"2012-10-24T14:50:02","modified_gmt":"2012-10-24T14:50:02","slug":"database-files-objects-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2824","title":{"rendered":"Database Files & Objects – Part 3"},"content":{"rendered":"

\"\"<\/a>Today, I will be continuing my talk on database objects that can be viewed through the system catalog views<\/a> or system stored procedures<\/a>. A Microsoft SQL Server database is composed of many different objects. We will be reviewing how to retrieve information on these objects in the [MATH] sample database. <\/p>\n

Please see my first article that creates this database. I will be focusing on tables, columns, indexes, and constraints 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
The most basic component of a database are tables.  There are three distinct ways to get information about table structures.<\/p>\n
If we want to get a simple list of all the tables in a database, one can execute the sp_tables<\/a> system stored procedure.  This returns all tables and views in the current database.  Most of the 404 objects
\nare system tables and views.<\/p>\n
-- Older system stored proc (all tables & views)\r\nsp_tables\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
We can filter the information by calling the stored procedure with the name of the table that we are interested in.<\/p>\n
-- Older system stored proc - just one table\r\nsp_tables @table_name = 'TBL_PRIMES';\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The system catalog views, most of the time, supply more information than these legacy system store procedures.  If you query the sys.tables<\/a> catalog view, you will see each table in the database.  Nice thing about this view is that it only returns user defined tables.  Detailed information on object id, schema id, principle id, and etc will be shown.  The schema id can be used to obtain the schema name and the principle id will lead you to the owner of the table.<\/p>\n
-- List tables (user tables)\r\nselect * from sys.tables\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
A more generic stored procedure that returns a wealth of information is sp_help<\/a>.  This takes an object name as a parameter and returns a bunch of information.  In our example we get the table name, columns, indentity column, rowguid column, constraints, indexes, primary and foreign keys.  Wow, make sure you change your output to text to see it all in one viewing.<\/p>\n
-- Detailed information on the table\r\nsp_help @objname = 'TBL_PRIMES';\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
large screen shot #1<\/a><\/p>\n
The most basic component of a table are columns.  There are two ways to get information on them.  The first way is to call the sp_columns<\/a> with our table name as a parameter.  This is an important filter not to forget.<\/p>\n
-- Older system stored proc - list by object\r\nsp_columns 'TBL_PRIMES'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The second way, a query of the sys.columns<\/a> catalog view will show all columns for a given table or view.  We can use the OBJECT_ID()<\/a> system function to return a id given a table or view name.  However, the system types are encoded in this view.  An additional join to the sys.types will decode the values into names.<\/p>\n
It is very import to filter by object id since table-valued assembly functions (FT), inline table-valued SQL functions (IF), internal tables (IT), system tables (S), table-valued SQL functions (TF), user tables (U), and views (V) are all considered to have columns.  Otherwise, we would end up with 8597 rows for each column in the database.  Most columns are inherited system objects.<\/p>\n
-- My columns in my table\r\nselect b.name, * \r\nfrom sys.columns a join sys.types b \r\non a.system_type_id = b.system_type_id\r\nwhere a.object_id = OBJECT_ID('DBO.TBL_PRIMES')\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
For good performance on adhoc queries, a table should be indexed with a primary key.  We can use the sp_helpindex<\/a> system stored procedure to get information on this index.<\/p>\n
-- Older system stored proc - all indexes\r\nsp_helpindex 'TBL_PRIMES'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Like most examples today, there is a system catalog named sys.indexes<\/a> view that can be queried to get the same information.<\/p>\n
-- My clustered index\r\nselect * from sys.indexes where name like 'PK%'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
To demonstrate foreign keys, I am going to alter the [MATH] sample database to have a table that contains a sub-set of primes.  The TSQL snippet below creates a new table with a calculated column, a foreign key and a identity column.  It loads the new table with the top most 500 prime numbers.<\/p>\n
-- Remove table if it exists\r\nIF OBJECT_ID('[DBO].[TBL_TOP_500_PRIMES]') > 0 \r\nBEGIN\r\n    DROP TABLE [DBO].[TBL_TOP_500_PRIMES]\r\nEND\r\n\r\n-- Create table to hold subset\r\nCREATE TABLE [DBO].[TBL_TOP_500_PRIMES]\r\n(\r\n\t[MY_ID] [int] identity (1, 1) NOT NULL,\r\n\t[MY_VALUE] [bigint] NOT NULL,\r\n\t[MY_DIVISION] [bigint] NOT NULL,\r\n\t[MY_SEARCH_PCT] AS (CAST([MY_DIVISION] AS REAL) \/ CAST([MY_VALUE] AS REAL) * 100.00),\r\n\tCONSTRAINT [PK_TBL_TOP_500_PRIMES] PRIMARY KEY CLUSTERED ( [MY_VALUE] ASC )\r\n) ON [PRIMARY]\r\nGO\r\n\r\n-- Add foreign key \r\nALTER TABLE [DBO].[TBL_TOP_500_PRIMES] WITH CHECK ADD CONSTRAINT [FK_TBL_PRIMES] FOREIGN KEY([MY_VALUE])\r\nREFERENCES [DBO].[TBL_PRIMES] ([MY_VALUE])\r\nGO\r\n\r\n-- Load table with data\r\nINSERT INTO [DBO].[TBL_TOP_500_PRIMES] (MY_VALUE, MY_DIVISION)\r\nSELECT TOP 500 MY_VALUE, MY_DIVISION FROM [DBO].[TBL_PRIMES] ORDER BY MY_VALUE ASC\r\nGO\r\n\r\n-- Show the data in the table\r\nSELECT * FROM [DBO].[TBL_TOP_500_PRIMES]\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
large screen shot #2<\/a><\/p>\n
With this new table comes the opportunity to look at two more column types.  A query against the system catalog named sys.computed_columns<\/a> will show us the [MY_SEARCH_PCT] column.<\/p>\n
-- Show columns that are computed\r\nselect * from sys.computed_columns\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
 A query against the system catalog named sys.identity_columns<\/a> will show us the [MY_ID] column.<\/p>\n
-- Show columns that are identity\r\nselect * from sys.identity_columns where name like 'MY%'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
While a primary key is a clustered index by default, it is also has an uniqueness condition.  You can not have two records with the same primary key.  This violates 3rd normal form.  We can use the sp_pkeys<\/a> to examine the primary key on a named table.<\/p>\n
-- Older system stored proc - just primary key\r\nsp_pkeys 'TBL_PRIMES'\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Again, there is a system catalog view named sys.key_constraints<\/a> that can be queried to get information on primary or unique constraints.<\/p>\n
-- Sub-view of sys.objects\r\nselect * from sys.key_constraints\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
A constraint can be classified as default, check, unique, foreign or primary.  Since we have already talked about primary keys, we are going to examine a couple of the other types.<\/p>\n
The sys.check_constraints<\/a> view can be used to see what check constraints exist in the current database.<\/p>\n
-- Sub-view of sys.objects\r\nselect * from sys.check_constraints \r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The sys.default_constraints<\/a> view can be used to see what default constraints exist in the current database.<\/p>\n
-- Sub-view of sys.objects\r\nselect * from sys.default_constraints\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The sys.foreign_keys<\/a> view can be used to see what foreign constraints exist in the current database.<\/p>\n
-- Sub-view of sys.objects\r\nselect * from sys.foreign_keys\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The sys.objects<\/a> view is the catch all place for objects in the database.  If we filter on the various types of constraints:  C – check constraint, D – default constraint, UQ – unique constraint, F – foreign key constraint, PK – primary key constraint, we can get detailed information that we saw in the previous stored procedure calls.<\/p>\n
-- All my constraints\r\nselect * from sys.objects where is_ms_shipped = 0 and type in ('C', 'D', 'UQ', 'PK', 'F')\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Last but not least, the sp_helpconstraint<\/a> stored procedure can be used to return information on all constraints given a table name.<\/p>\n
-- Returns a bunch of info.\r\nsp_helpconstraint @objname = 'TBL_PRIMES'\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 the databases object such as tables, columns, indexes, and constraints.  Sometimes, system catalog views have more detailed information on the objects you want to research.  In the future, I am sure I will be designing T-SQL 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:  synonyms, views, functions, triggers and stored procedures.<\/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 reviewing 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 tables, columns, indexes, and constraints in this article. Let’s use the USE statement to select the sample database. — Which database to use. USE [MATH] go…<\/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,569,12,15,572,522,579,573,575,570,28,577,523,580,578,582,581,574,576,325,571,568,567,29],"class_list":["post-2824","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-database-developer","tag-dynamic-management-views","tag-free-code","tag-john-f-miner-iii","tag-sp_columns","tag-sp_help","tag-sp_helpconstraint","tag-sp_helpindex","tag-sp_pkeys","tag-sp_tables","tag-sql-server","tag-sys-check_constraints","tag-sys-columns","tag-sys-computed_columns","tag-sys-default_constraints","tag-sys-foreign_keys","tag-sys-identity_columns","tag-sys-indexes","tag-sys-key_constraints","tag-sys-objects","tag-sys-tables","tag-system-catalog-views","tag-system-stored-procedures","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2824","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=2824"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2824\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2824"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2824"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2824"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}