Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
From the author of

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     
  • + Share This
  • 🔖 Save To Your Account