Mysql primary key-related SQL statement collection _mysql

Add a table field

ALTER TABLE table1 add transactor varchar (a) not Null;

ALTER TABLE table1 add ID int unsigned not Null auto_increment primary key

Modify the field type of a table and specify null or Non-empty

ALTER TABLE name change field Name field name FieldSize field type [Allow Non-null];

ALTER TABLE name modify field Name field type [Allow Non-null];

ALTER TABLE name modify field Name field type [Allow Non-null];

Modify the field name of a table and specify null or Non-empty

ALTER TABLE name change field original Name field new Name field type [Allow Non-empty

Delete a field

ALTER TABLE mytable DROP field name;

Add a unique key

ALTER TABLE ' test2 ' ADD UNIQUE (' userid ')

modifying primary keys

ALTER TABLE ' test2 ' DROP PRIMARY key, ADD PRIMARY key (' ID ')

Add index

ALTER TABLE ' test2 ' ADD INDEX (' id ')

ALTER TABLE ' category ' MODIFY COLUMN ' id ' int (one) not NULL auto_increment A-I, ADD PRIMARY KEY (' id ');

The SQL statement block that modifies the primary key is as follows:

DECLARE @defname varchar (100)
DECLARE @cmd varchar (500)
DECLARE @tablename varchar (100)
DECLARE @keyname varchar (100)
Set @tablename = ' TEMP1 '
Set @keyname = ' id '--a key that requires setup, separating
Select @defname = Name
From sysobjects so
JOIN Sysconstraints SC
On so.id = Sc.constid
WHERE object_name (so.parent_obj) = @tablename
and xtype= ' PK '
If @defname is not null
Begin
Select @cmd = ' ALTER TABLE ' + @tablename + ' drop constraint ' + @defname
--print @cmd
EXEC (@cmd)
End
Else
Set @defname = ' pk_ ' + @keyname
Select @cmd = ' ALTER TABLE ' + @tablename + ' ADD constraint ' + @defname + ' PRIMARY KEY CLUSTERED (' + @keyname + ') '
EXEC (@cmd)

How to take the primary key field name and field type--Get the primary key field name

1:
SELECT Table_name,column_name from INFORMATION_SCHEMA. Key_column_usage

WHERE table_name<> ' dtproperties '

2:
EXEC Sp_pkeys @table_name = ' table name '

3:
Select O.name as table name, c.name as field name, K.colid as field ordinal, K.keyno as index order, t.name as type

From sysindexes i

Join Sysindexkeys k on i.id = k.id and I.indid = K.indid

Join sysobjects o on i.id = o.id

Join syscolumns C on i.id=c.id and k.colid = C.colid

Join Systypes T on C.xusertype=t.xusertype

where O.xtype = ' U ' and o.name= ' table name to query

and exists (select 1 from sysobjects where xtype = ' PK ' and parent_obj=i.id and name = I.name)

ORDER BY O.name,k.colid