show primary key info
sp_pkeys EMPLOYEE
set a explicit pk name during creating table
CREATE TABLE EMPLOYEE
(
ID int Not Null CONSTRAINT pk_EMPLOYEE_ID PRIMARY KEY,
FirstName nvarchar(20),
LastName nvarchar(20)
)
or
CREATE TABLE EMPLOYEE
(
ID int Not Null,
FirstName nvarchar(20),
LastName nvarchar(20),
DEPARTMENT_ID int,
CONSTRAINT pk_EMPLOYEE_ID PRIMARY KEY (ID)
)
set a explicit pk name to an existing table
ALTER TABLE EMPLOYEE ADD CONSTRAINT pk_ID PRIMARY KEY (ID)
set a explicit fk name to an existing table
ALTER TABLE EMPLOYEE ADD CONSTRAINT fk_FirstName FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENT(DEPARTMENT_ID)
show foreign key name
SELECT name FROM sysobjects WHERE parent_obj=OBJECT_ID('Account') AND xtype='F'
show primary key name
SELECT name FROM sysobjects WHERE parent_obj=OBJECT_ID('Account') AND xtype='PK'
alter table add column
ALTER TABLE Account ADD ClientRef nvarchar(128) null;
alter table drop column
ALTER TABLE Account DROP COLUMN ClientRef;
随机取得表中任意N条记录的方法
很简单,就用newid():
select top N * from table_name order by newid() ----N是一个你指定的整数,表是取得记录的条数
随机数rand()的用法
select floor(rand()*N) ---生成的数是这样的:12.0
select cast( floor(rand()*N) as int) ---生成的数是这样的:12
select ceiling(rand() * N) ---生成的数是这样的:12.0
select cast(ceiling(rand() * N) as int) ---生成的数是这样的:12
update Account set ClientRef = 'ClientRef' + (CAST(CAST(RAND()*1000 as int) as nvarchar))