数据库技术:Sql Server数据库常用Transact-SQL脚本

use master ;    go    create database sales    on     ( name = sales_dat,        filename = 'c:program filesmicrosoft sql servermssql13.mssqlservermssqldatasaledat.mdf',        size = 10,        maxsize = 50,        filegrowth = 5 )    log on    ( name = sales_log,        filename = 'c:program filesmicrosoft sql servermssql13.mssqlservermssqldatasalelog.ldf',        size = 5mb,        maxsize = 25mb,        filegrowth = 5mb ) ;    go 

select name, database_id, create_date    from sys.databases ;  

drop database sales;

create table purchaseorderdetail    (        id uniqueidentifier not null        ,linenumber smallint not null        ,productid int null        ,unitprice money null        ,orderqty smallint null        ,receivedqty float null        ,rejectedqty float null        ,duedate datetime null    );  

drop table dbo.purchaseorderdetail;  

exec sp_rename 'sales.salesterritory', 'salesterr';  

alter table dbo.doc_exa add column_b varchar(20) null, column_c int null ;

alter table dbo.doc_exb drop column column_b;

exec sp_rename 'sales.salesterritory.territoryid', 'terrid', 'column';

--在现有表中创建主键  alter table production.transactionhistoryarchive     add constraint pk_transactionhistoryarchive_transactionid primary key clustered (transactionid);    --在新表中创建主键  create table production.transactionhistoryarchive1     (        transactionid int identity (1,1) not null        , constraint pk_transactionhistoryarchive_transactionid primary key clustered (transactionid)     )  ;      --查看主键    select name    from sys.key_constraints    where type = 'pk' and object_name(parent_object_id) = n'transactionhistoryarchive';    go      --删除主键  alter table production.transactionhistoryarchive    drop constraint pk_transactionhistoryarchive_transactionid;     go  

create view v_employeehiredate    as    select p.firstname, p.lastname, e.hiredate    from humanresources.employee as e join person.person as  p    on e.businessentityid = p.businessentityid ;     go  

drop view v_employeehiredate;  

create procedure p_uspgetemployeestest         @lastname nvarchar(50),         @firstname nvarchar(50)     as         select firstname, lastname, department        from humanresources.vemployeedepartmenthistory        where firstname = @firstname and lastname = @lastname        and enddate is null;    go  

drop procedure p_uspgetemployeestest; 

exec p_uspgetemployeestest n'ackerman', n'pilar';    -- or    exec p_uspgetemployeestest @lastname = n'ackerman', @firstname = n'pilar';    go    -- or    execute p_uspgetemployeestest @firstname = n'pilar', @lastname = n'ackerman';    go  

exec sp_rename 'p_uspgetallemployeestest', 'p_uspeveryemployeetest2'; 

create procedure p_uspgetemployeesalesytd    @salesperson nvarchar(50),    @salesytd money output    as          select @salesytd = salesytd        from salesperson as sp        join vemployee as e on e.businessentityid = sp.businessentityid        where lastname = @salesperson;    return    go      --调用  declare @salesytdbysalesperson money;    execute p_uspgetemployeesalesytd        n'blythe',       @salesytd = @salesytdbysalesperson output;   go