用批处理的形式建库建表

--创建库存储过程

Create PROCEDURE [dbo].[CreateDataBaseName]( @basename varchar(30))
 -- Add the parameters for the stored procedure here

AS
declare @sql varchar(1000)
declare @prompt varchar(30)
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
    set @prompt=''
 
 SELECT @prompt=[Name] FROM Master..SysDatabases
 where [name] = @basename
 
 if @prompt <> ''
       begin
   set @sql ='@echo 数据库已存在，请重新输入要创建的库名!'
   print(@sql)
    end
 else
    begin
  set @sql = 'CREATE DATABASE ['+@basename+'] ON  PRIMARY '+
       '( NAME = '''+@basename+''', FILENAME = ''C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/'+@basename+'.mdf'' , SIZE = 3072KB , FILEGROWTH = 1024KB )'+
       ' LOG ON '+
       '( NAME = '''+@basename+'_log'', FILENAME =''C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/'+@basename+'_log.ldf'' , SIZE = 1024KB , FILEGROWTH = 10%) '

  exec(@sql)
    end
 
END

 

 

 

--建表存储过程

 

Create PROCEDURE [dbo].[CreateTableName]( @oribasename varchar(30),@destbasename varchar(30))
 -- Add the parameters for the stored procedure here

AS
declare @sql varchar(1000)
declare @dott char(1);
BEGIN
 
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
    set @dott='.'
 set @sql ='select ''sqlcmd -S tcp:172.16.3.53/ZHANGXZ-4B7215C,1433 -U xep -P xep -d attc -q'+'" select * into ''+'+
              ''''+@destbasename+''+@dott+'''+u.[name]+'''
              +@dott+'''+o.[name]+'' from  '+@oribasename+''+
              @dott+'''+u.[name]+'''+@dott+'''+''[''+o.[name]+'']  "''from '
              +@oribasename+'..sysobjects o
              inner join '+@oribasename+'..sysusers u on o.uid = u.uid where o.xtype= ''u'' order by o.[name]'
    exec(@Sql)
END

 

--批处理文件 createbase.bat

@if {%1}=={}  goto :usage
@sqlcmd -S tcp:172.16.3.53/ZHANGXZ-4B7215C,1433 -U xep -P xep -d attc -h-1 -q " exec dbo.CreateDataBasename '%1' " -o "c:/cb1.bat"
@call c:/cb1.bat
@goto :end

:usage
@echo 请输入数据库名称!
:end

在dos下 输入 createbase.bat basename