create table person( persnr varchar(11), navn varchar(40), primary key(persnr) );
I)exec sp_tables; TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS ----------------- -------------------- ---------------------- ---------------------- ----------------------- elinkaan dbo syscolumns SYSTEM TABLE NULL elinkaan dbo syscomments SYSTEM TABLE NULL elinkaan dbo sysdepends SYSTEM TABLE NULL elinkaan dbo sysfilegroups SYSTEM TABLE NULL elinkaan dbo sysfiles SYSTEM TABLE NULL elinkaan dbo sysfiles1 SYSTEM TABLE NULL elinkaan dbo sysforeignkeys SYSTEM TABLE NULL elinkaan dbo sysfulltextcatalogs SYSTEM TABLE NULL elinkaan dbo sysfulltextnotify SYSTEM TABLE NULL elinkaan dbo sysindexes SYSTEM TABLE NULL elinkaan dbo sysindexkeys SYSTEM TABLE NULL elinkaan dbo sysmembers SYSTEM TABLE NULL elinkaan dbo sysobjects SYSTEM TABLE NULL elinkaan dbo syspermissions SYSTEM TABLE NULL elinkaan dbo sysproperties SYSTEM TABLE NULL elinkaan dbo sysprotects SYSTEM TABLE NULL elinkaan dbo sysreferences SYSTEM TABLE NULL elinkaan dbo systypes SYSTEM TABLE NULL elinkaan dbo sysusers SYSTEM TABLE NULL elinkaan dbo dtproperties TABLE NULL elinkaan dbo ferieListe TABLE NULL elinkaan dbo sysconstraints VIEW NULL elinkaan dbo syssegments VIEW NULL (24 row(s) affected)
II)exec sp_help; Name Owner Object_type -------------------------------- ---------------------------------- ------------------------------- sysconstraints dbo view syssegments dbo view dtproperties dbo user table person dbo user table syscolumns dbo system table syscomments dbo system table sysdepends dbo system table sysfilegroups dbo system table sysfiles dbo system table sysfiles1 dbo system table sysforeignkeys dbo system table sysfulltextcatalogs dbo system table sysfulltextnotify dbo system table sysindexes dbo system table sysindexkeys dbo system table sysmembers dbo system table sysobjects dbo system table syspermissions dbo system table sysproperties dbo system table sysprotects dbo system table sysreferences dbo system table systypes dbo system table sysusers dbo system table dt_addtosourcecontrol dbo stored procedure dt_addtosourcecontrol_u dbo stored procedure dt_adduserobject dbo stored procedure dt_adduserobject_vcs dbo stored procedure dt_checkinobject dbo stored procedure dt_checkinobject_u dbo stored procedure dt_checkoutobject dbo stored procedure dt_checkoutobject_u dbo stored procedure dt_displayoaerror dbo stored procedure dt_displayoaerror_u dbo stored procedure dt_droppropertiesbyid dbo stored procedure dt_dropuserobjectbyid dbo stored procedure dt_generateansiname dbo stored procedure dt_getobjwithprop dbo stored procedure dt_getobjwithprop_u dbo stored procedure dt_getpropertiesbyid dbo stored procedure dt_getpropertiesbyid_u dbo stored procedure dt_getpropertiesbyid_vcs dbo stored procedure dt_getpropertiesbyid_vcs_u dbo stored procedure dt_isundersourcecontrol dbo stored procedure dt_isundersourcecontrol_u dbo stored procedure dt_removefromsourcecontrol dbo stored procedure dt_setpropertybyid dbo stored procedure dt_setpropertybyid_u dbo stored procedure dt_validateloginparams dbo stored procedure dt_validateloginparams_u dbo stored procedure dt_vcsenabled dbo stored procedure dt_verstamp006 dbo stored procedure dt_whocheckedout dbo stored procedure dt_whocheckedout_u dbo stored procedure PK__ferieListe__38EE7070 dbo primary key cns pk_dtproperties dbo primary key cns DF__dtpropert__versi__50FB042B dbo default (maybe cns) User_type Storage_type Length Prec Scale Nullable Default_name Rule_name Collation -------------- ----------------- ------- ----------- ----------- ------------- --------------- ------------ ------------
exec sp_columns @table_name=person; 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 ---------------- ----------------- ------------------- ---------- ---------- ---------- ------- ------ ------ --------- -------- ----------- -------------- ----------------- ------------------ ----------------- ------------ ------------ elinkaan dbo person persnr 12 varchar 11 11 NULL NULL 0 NULL NULL 12 NULL 11 1 NO 39 elinkaan dbo person navn 12 varchar 40 40 NULL NULL 1 NULL NULL 12 NULL 40 2 YES 39 (2 row(s) affected)
exec sp_pkeys @table_name=person; TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME ---------------- ------------ ----------- ------------ -------- -------------------- elinkaan dbo person persnr 1 PK__person__3AD6B8E2 (1 row(s) affected)
Elin K. Ajer Andreassen, 2003