| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Transact-SQL User's Guide |
![[-]](/dwicons/i_colpse.gif)
| Chapter 7 Creating Databases and Tables |
|
| Getting information about databases and tables |
Adaptive Server includes several procedures and functions you can use to get information about databases, tables, and other database objects. This section describes some of them. For complete information, see the Reference Manual.
sp_helpdb can report information about a specified database or about all Adaptive Server databases. It reports the name, size, and usage of each fragment you have assigned to the database.
sp_helpdb [dbname]
For example, to display a report on pubs2:
sp_helpdb pubs2
name db_size owner dbid created status ----- ------- ------ ---- --------------- ------------- pubs2 2 MB sa 4 Jun 18 1997 no options set (1 row affected)
device size usage ----------------- ----------- -------------- pubsdev 2 MB data + log (1 row affected)
sp_databases lists all the databases on a server. For example:
sp_databases
database_name database_size remarks ----------------- ------------- ------------ master 5120 NULL model 2048 NULL pubs2 2048 NULL pubs3 2048 NULL sybsecurity 5120 NULL sybsystemprocs 30720 NULL tempdb 2048 NULL (7 rows affected, return status = 0)
To find out who owns a database, use sp_helpuser:
sp_helpuser dbo
Users_name ID_in_db Group_name Login_name ------------- -------- ------------ ------------ dbo 1 public sa (return status = 0)
Use db_id() and db_name() to identify the current database. For example:
select db_name(), db_id()
------------------------------ ------ master 1
Adaptive Server provides system procedures, catalog stored procedures, and built-in functions that return helpful information about database objects such as tables, columns, and constraints.
Using sp_help on database objectsUse sp_help to display information about a specified database object (that is, any object listed in sysobjects), a specified datatype (listed in systypes), or all objects and datatypes in the current database.
sp_help [objname]
Here is the output for the publishers table:
Name Owner Type -------------------------- ----------- ---------- publisher dbo user table (1 row affected)
Data_located_on_segment When_created ------------------------------ -------------------- default Jul 7 1997 1:43PM
Column_name Type Length Prec Scale
----------- ------- ------ ----- -----
pub_id char 4 NULL NULL
pub_name varchar 40 NULL NULL
city varchar 20 NULL NULL
state char 2 NULL NULL
Nulls Default_name Rule_name Identity
----- ------------- --------- --------
0 NULL pub_idrule 0
1 NULL NULL 0
1 NULL NULL 0
1 NULL NULL 0
index_name index_description index_keys index_max_rows_per_page
---------- ------------------ ---------- -----------------------
pubind clustered, unique pub_id 0
located on default
(1 row affected)keytype object related_object related_keys ------- ---------- -------------- ------------ primary publishers -- none -- pub_id, *, *, *, *, * foreign titles publishers pub_id, *, *, *, *, * (1 row affected)
Object is not partitioned. (return status = 0)
If you execute sp_help without supplying an object name, the resulting report shows each object in sysobjects, along with its name, owner, and object type. Also shown is each user-defined datatype in systypes and its name, storage type, length, whether null values are allowed, and any defaults or rules bound to it. The report also notes if any primary or foreign key columns have been defined for a table or view.
sp_help lists any indexes on a table, including those created by defining unique or primary key constraints. However, it does not describe any information about the integrity constraints defined for a table. Use sp_helpconstraint to display information about any integrity constraints.
Using sp_helpconstraint to find a table's constraint informationsp_helpconstraint reports information about the declarative referential integrity constraints specified for a table, including the constraint name and definition of the default, unique or primary key constraint, referential, or check constraint. sp_helpconstraint also reports the number of references associated with the specified tables.
Its syntax is:
sp_helpconstraint [objname] [, detail]
objname is the name of the table being queried. If you do not include a table name, sp_helpconstraint displays the number of references associated with each table in the current database. With a table name, sp_helpconstraint reports the name, definition, and number of integrity constraints associated with the table. The detail option also returns information about the constraint's user or error messages.
For example, suppose you run sp_helpconstraint on the store_employees table in pubs3.
name defn
--------------------------- --------------------------------
store_empl_stor_i_272004000 store_employees FOREIGN KEY
(stor_id) REFERENCES stores(stor_id)
store_empl_mgr_id_288004057 store_employees FOREIGN KEY
(mgr_id) SELF REFERENCES
store_employees(emp_id)
store_empl_2560039432 UNIQUE INDEX( emp_id) :
NONCLUSTERED, FOREIGN REFERENCE
(3 rows affected)
Total Number of Referential Constraints: 2
Details:
-- Number of references made by this table: 2
-- Number of references to this table: 1
-- Number of self references to this table: 1
Formula for Calculation:
Total Number of Referential Constraints
= Number of references made by this table
+ Number of references made to this table
- Number of self references within this tableTo find the largest number of referential constraints associated with any table in the current database, run sp_helpconstraint without specifying a table name, for example:
sp_helpconstraint
id name Num_referential_constraints ----------- ------------------------ --------------------------- 80003316 titles 4 16003088 authors 3 176003658 stores 3 256003943 salesdetail 3 208003772 sales 2 336004228 titleauthor 2 896006223 store_employees 2 48003202 publishers 1 128003487 roysched 1 400004456 discounts 1 448004627 au_pix 1 496004798 blurbs 1 (11 rows affected)
In this report, the titles table has the largest number of referential constraints in the pubs3 database.
Finding out how much space a table usesUse sp_spaceused to find out how much space a table uses:
sp_spaceused [objname]
sp_spaceused computes and displays the number of rows and data pages used by a table or a clustered or nonclustered index.
To display a report on the space used by the titles table:
sp_spaceused titles
name rows reserved data index_size unused ------- ----- --------- ----- --------- ------ titles 18 48 KB 6 KB 4 KB 38 KB (0 rows affected)
If you do not include an object name, sp_spaceused displays a summary of space used by all database objects.
Listing tables, columns, and datatypesCatalog stored procedures retrieve information from the system tables in tabular form. You can supply wildcard characters for some parameters.
sp_tables lists all user tables in a database when used in the following format:
sp_tables @table_type = "'TABLE'"
sp_columns returns the datatype of any or all columns in one or more tables in a database. You can use wildcard characters to get information about more than one table or column.
For example, the following command returns information about all columns that includes the string "id" in all the tables with "sales" in their name:
sp_columns "%sales%", null, null, "%id%"
table_qualifier table_owner
table_name column_name
data_type type_name precision length scale radix nullable
remarks
ss_data_type colid
--------------- -----------
---------- -----------
--------- --------- --------- ------ ----- ----- --------
-------
------------ -----
pubs2 dbo
sales stor_id
1 char 4 4 NULL NULL 0
NULL
47 1
pubs2 dbo
salesdetail stor_id
1 char 4 4 NULL NULL 0
NULL
4 1
pubs dbo
salesdetail title_id
12 varchar 6 6 NULL NULL 0
NULL
39 3
(3 rows affected, return status = 0)Finding an object name and
IDUse object_id() and object_name() to identify the ID and name of an object. For example:
select object_id("titles")---------- 208003772
Object names and IDs are stored in the sysobjects system table.
|
|