sqlsever存储过程配合代理作业自动定时建表

1.自动建表存储过程

use [threetoone]

go

/****** object:  storedprocedure [dbo].[wto_createtable_scandoxxx]    script date: 01/08/2019 15:20:09 ******/

set ansi_nulls on

go

set quoted_identifier on

go

-- =============================================

-- author:    litiantian

-- create date: 2018/07/12

-- description:   

-- =============================================

alter procedure [dbo].[wto_createtable_scandoxxx]

    -- add the parameters for the stored procedure here

    --<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,

    --<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>

as

declare @year varchar(4)

declare @month varchar(4)

declare @target varchar(18)

declare @str  varchar(8000)

 

set @year = datepart(yyyy,getdate())

set @month = datepart(week,getdate()) +1

set @month=replace(right(str(@month),4),' ','0')

 

set @target= 'scando_' + @year  + @month

 

if not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[' + @target + ']') and objectproperty(id, n'isusertable') = 1)

 

begin

 

set @str = '

create table [dbo].[' + @target + '] (

    [id] [int] identity(1,1) not null,

    [facode] [varchar](50)  not null,

    [sncode] [varchar](25) null,

    [bicode] [varchar](25) null,

    [mdcode] [varchar](15) null,

    [location] [varchar](50) null,

    [carnum] [varchar](50) null,

    [localnum] [varchar](50) null,

    [flagfc] [varchar](50) null,

    [flagcl] [varchar](50) null,

    [userid] [varchar](10) null,

    [logonmac] [varchar](50) null,

    [workname] [varchar](20) null,

    [writedate] [datetime] default (convert([varchar],getdate(),(120))) null,

    [beforloca] [varchar](50) null,

) on [primary]

'

exec (@str)

 

set @str = 'alter table ' + @target + ' add primary key (id,facode)'

 

exec (@str)

 

end

2.配合代理作业定时执行存储过程