Today we’re going to explore a view that is quite a bit more useful. First though, I spent a little time rewriting the create table scripts so we could fully explore a lot of the features these tables and SSCE has to offer.
Drop the two tables you created yesterday, or just create a new database. Then you can run these statements:
create table employee (
empid uniqueidentifier not null,
last nvarchar(100),
first nvarchar(100),
birth datetime,
constraint pk_empid primary key (empid)
);
create table parts (
partid bigint IDENTITY(100,1) not null,
partname nvarchar(100) not null,
qtyonhand int default 0,
constraint pk_parts primary key (partid)
);
create table inventory (
partid bigint not null,
binid bigint not null,
location nvarchar(50),
constraint pk_inventory primary key (partid, binid),
constraint fk_part foreign key (partid) references parts(partid)
);
As you can see, the new version of parts uses a bigint for the primary key, and I’ve set it up as an identity column. The 100, 1 after identity tells SSCE to start the first number at 100, and increment by 1.
For use in a later example I’ve also redone the inventory table and added a part table. I’ve made the index for the inventory table two parts, and added a foreign key back to the parts table. Now let’s take a look at the Columns view.
Like many of the views, a simple select * is going to return a lot of empty fields. Thus I have created the following statement which returns the most useful fields to us.
select table_name, column_name, ordinal_position,
column_hasdefault, column_flags, is_nullable, data_type,
character_maximum_length, numeric_precision, numeric_scale,
datetime_precision, autoinc_min, autoinc_max, autoinc_next,
autoinc_seed, autoinc_increment
from information_schema.columns
I won’t go over every single field, but let me point out a few of the more useful ones. The table_name, column_name, and data_type fields are pretty obvious and are probably the ones you’ll use the most.
Column_hasdefault is a Boolean, 1 indicates there’s a default value, 0 indicates there’s no default. Character_maximum_length is just as it describes, for character types it indicates the max length, for non characters it will be null.
The autoinc_* fields are only valid for ints or bigints being used as identity columns, such as the partid field in the parts table. Min and max are obvious, describe the bounds of the field. Seed simply shows what the starting value is. This can be handy if you want to know what value to start reading from. Increment shows the value to add when creating the next primary key.
The most handy though is Next. Next will show you what the next primary key will be. This can be useful if the user is creating a new record, and you’d like to go ahead and show them what the new primary key will be without wanting to create the record.
Finally is ordinal_position. This field indicates the order the fields occur in the table. Use this for sorting, if you want to display the fields in the same order as the database: order by ordinal_position.
Arcane Code 