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.配合代理作业定时执行存储过程