--Create TABLE table (A1 varchar, a2 char (2))--Add description information for table execute sp_addextendedproperty n ' ms_description ', ' Personnel information table ', n ' User ', n ' dbo ', n ' table ', n ' table ', NULL, null--Add description information for field A1 Execute sp_addextendedproperty n ' ms_description ', ' name ', n ' user ', n ' dbo ', n ' table ', n ' tables ', n ' column ', n ' A1 '--Add description information for field A2 execute sp_addextendedproperty n ' ms_description ', ' Gender ', n ' user ', n ' dbo ', n ' table ', n ' tables ', n ' column ', n ' A2 '--Update the Description property of the column A1 in the table: EXEC sp_updateextendedproperty ' ms_description ', ' Field 1 ', ' user ', dbo, ' table ', ' Tables ', ' column ', A1--Delete the description attribute of column A1 in the table: EXEC sp_dropextendedproperty ' ms_description ', ' user ', dbo, ' table ', ' Tables ', ' Column ', A1--T_workinfo_template work Information template if object_id (' t_workinfo_template ') is not NULL DROP TABLE t_workinfo_ Templategocreate TABLE t_workinfo_template (workinfo_template_id CHAR) PRIMARY key,--primary key Componentreference VARCHAR ( ),--Related Equipment itemname varchar (itemcontent),--maintenance content period NUMERIC (9,2),--Pre-inspection period periodical varchar (10 ),--the unit of the period, the data takes T_periodicalremark varchar (4000),--Note class varchar (20)--category) on [primary]goexec sp_addextendedproperty n ' ms_description ', n ' primary key ', n ' user ', n ' dbo ', n ' table ', n ' t_workinfo_ TEMPLATE ', n ' column ', n ' workinfo_template_id ' exec sp_addextendedproperty n ' ms_description ', n ' related devices ', n ' user ', n ' dbo ', n ' table ', n ' t_workinfo_template ', n ' column ', n ' componentreference ' exec sp_addextendedproperty n ' ms_description ', n ' Maintenance item ', n ' user ', n ' dbo ', n ' table ', n ' t_workinfo_template ', n ' column ', n ' itemname ' exec sp_addextendedproperty n ' ms_ Description ', n ' maintenance content ', n ' user ', n ' dbo ', n ' table ', n ' t_workinfo_template ', n ' column ', n ' itemcontent ' exec sp_ Addextendedproperty n ' ms_description ', n ' preflight period ', n ' user ', n ' dbo ', n ' table ', n ' t_workinfo_template ', n ' column ', n ' PERIOD ' EXEC sp_addextendedproperty n ' ms_description ', n ' cycles of units, data take t_periodical ', n ' user ', n ' dbo ', n ' table ', n ' t_workinfo_ TEMPLATE ', n ' column ', n ' periodical ' EXEC sp_addextendedproperty n ' ms_description ', n ' Memo ', n ' user ', n ' dbo ', n ' table ', n ' T _workinfo_template ', n ' column ', n ' REMARK ' GO ALTER table Table ADD table1_cn char (200)--Add field ALTER TABLE Table Drop COLUMN table1_cn--delete field ALTER TABLE table ALTER COLUMN TABLE1_CN NVARCHAR (50)--Modify field type EXEC s P_rename ' table. Table1_cn ', ' filedName2 ', ' COLUMN '--Modify the Field Name drop table Table--Delete tables--Determine if the fields in a table filedName1 exist if exists (SELECT * FROM syscolumns where id=object_id (' table ') and name= ' filedName2 ') print ' field already exists ' else print ' field does not exist '--list table out all field names select name fro M syscolumns where id=object_id (' smenulist ')--Add primary key ALTER TABLE TABNAME add primary key (COL)--delete primary key ALTER TABLE tabname drop p Rimary Key (COL)--Creating an index create [unique] index idxname on tabname (COL)--delete index drop indexes idxname/* create BBSDB database */use Masterif Exists (SELECT * from sysdatabases where name= ' bbsdb ') BeginPrint ' database has been created and cannot be recreated ' endelsebegincreate db Bbsdbon ( Name= ' Bbsdb_data ', filename= ' F:\MS Server db\bbsdb_data.mdf ', size=10,filegrowth=20%) log on (name= ' Bbsdb_log ', Filename= ' F:\MS Server db\bbsdb_log.ldf ', size=3,maxsize=20,filegrowth=10%) Print ' database creation complete ' End--drop databases Bbsdb
SQL Create/Modify database, table