- Exploring the Server
- Learning About Tables
- Learning About Fields
- An Example
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Learning About Fields
There are several ways you can learn about a table's fields. One method is to execute a select statement and then examine the results. For example, you can use this statement to load a DataTable object.
SELECT TOP 0 * FROM Students
The DataTable's Columns collection provides information about the underlying database table even though the SELECT statement selects no records.
In addition to this method, you can use system-stored procedures to learn about a table's columns. The following subsections describe some of these procedures.
Note that the program must be using a database before it can execute these procedures. For example, to use the sp_columns procedure to learn about the fields in the Students table in the TestScores database, the program must be connected to the TestScores database. A simple SQL script that connects to the database and executes the stored procedure would look like this:
USE TestScores; sp_columns Students;
sp_columns
The sp_columns stored procedure lists a lot of information about a table's columns. The following table lists values returned for each column. Some of the most useful values are COLUMN_NAME, TYPE_NAME, PRECISION, LENGTH, and IS_NULLABLE.
|
TABLE_QUALIFIER |
TABLE_OWNER |
TABLE_NAME |
COLUMN_NAME |
|
DATA_TYPE |
TYPE_NAME |
PRECISION |
LENGTH |
|
SCALE |
RADIX |
NULLABLE |
REMARKS |
|
COLUMN_DEF |
SQL_DATA_TYPE |
SQL_DATETIME_SUB |
CHAR_OCTET_LENGTH |
|
ORDINAL_POSITION |
IS_NULLABLE |
SS_DATA_TYPE |
|
sp_column_privileges
This series of articles doesn't talk about managing privileges (by book does) but you can use privileges to let different users read, update, and delete values in different tables, views, and even columns within a table. The sp_column_privileges stored procedure returns information about a table's column privileges.
The following output shows the interesting fields returned for the Students table. The abbreviation dbo means database owner.
COLUMN_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE =========== ======= ======= ========== ============ FirstName dbo dbo INSERT YES FirstName dbo dbo REFERENCES YES FirstName dbo dbo SELECT YES FirstName dbo dbo UPDATE YES LastName dbo dbo INSERT YES LastName dbo dbo REFERENCES YES LastName dbo dbo SELECT YES LastName dbo dbo UPDATE YES StudentId dbo dbo INSERT YES StudentId dbo dbo REFERENCES YES StudentId dbo dbo SELECT YES StudentId dbo dbo UPDATE YES