毕设之存储过程T-SQL

以下涉及到这些存储过程都是动态T-SQL语句，通过程序端输入数据库名、表名等参数实现存储过程的执行。

1、创建数据库

/*存储过程-创建数据库*/
create proc [dbo].[CreateDB](@dataname nvarchar(50),@maxsize nvarchar(10))
as 
begin
    declare @str nvarchar(500)
    set @str = 'create database ' + @dataname + ' on(name=' + @dataname + ',filename=''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' + @dataname
    + '.mdf'',size=10MB,maxsize=' + @maxsize + ',filegrowth=20%) log on(name=' + @dataname + '_log,filename=''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' + @dataname
    + '_log.ldf'',size=10MB,maxsize=' + @maxsize + ',filegrowth=20%)'
    print @str
    execute sp_executesql @str
end
GO

<补充>需要检查一下有没有这个夹“C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\”，这个文件夹默认存放数据库文件与日志文件，

不同类型与版本的数据库可能稍有不同。

 

2、创建表

/*存储过程-创建表*/
create proc [dbo].[CreateTable](@dataname nvarchar(50),@createstr nvarchar(4000))
as 
begin 
    declare @str nvarchar(4000)
    set @str = 'use ' + @dataname + ' ' + @createstr
    print @str
    execute sp_executesql @str
end 
GO

 

3、创建存储过程、视图、触发器

/*存储过程－创建存储过程、触发器、视图*/
create proc [dbo].[CreateProc](@dataname nvarchar(50),@createst nvarchar(400))
as 
begin 
    declare @str nvarchar(400)
    set @str = N'use ' + @dataname + '; exec sp_executesql N''' + @createst + '''';
    print @str
    execute sp_executesql @str
end 
GO

<补充>这个句子看似简单，但是花了我整整一天才写出来，原本以为它与创建表的T-SQL的结构是类似的，只需要将创建语句打包，然后再execute就行。
但试过之后，却发现不可行，最后还是google,在一个外文论坛上找到的答案。

 

4、获取表中数据

/*存储过程－获取表中数据*/
create proc [dbo].[Select_data](@dataname nvarchar(50),@tablename nvarchar(50))
as
begin
    declare @str nvarchar(200)
    set @str = 'use ' + @dataname + ' select * from ' + @tablename
    print @str
    execute sp_executesql @str
end
GO

 

5、获取表数据结构

/*存储过程－获取表数据结构*/
create proc [dbo].[Select_datalist](@dataname nvarchar(50),@tablename nvarchar(50))
as
begin
    declare @str nvarchar(2000)
    set @str = 'use ' + @dataname + ' select Column_id=C.column_id,ColumnName=C.name,PrimaryKey=ISNULL(IDX.PrimaryKey,N''''),
    [IDENTITY]=CASE when C.is_identity=1 then N''IDENTITY('' + RTRIM(IDENT_SEED(O.name)) + N'' , ''+ RTRIM(IDENT_INCR(O.name)) + N'' )'' else N'''' END,
    Type=T.name,Length=C.max_length,NullAble=case when C.is_nullable=1 then N''√''else N'''' END,[Default]=isnull(D.definition,N'''')' +' '+
    'from sys.columns C inner join sys.objects O on C.[object_id]=O.[object_id] and O.type = ''U'' and O.is_ms_shipped=0'+' '+
    'inner join sys.types T on C.user_type_id=T.user_type_id left join sys.default_constraints D on C.[object_id]=D.parent_object_id'+' '+
    'and C.column_id=D.parent_column_id and C.default_object_id=D.[object_id] left join sys.extended_properties PFD on PFD.class =1'+' '+
    'and C.[object_id]=PFD.major_id and C.column_id=PFD.minor_id left join sys.extended_properties PTB on PTB.class=1 and PTB.minor_id=0'+' '+
    'and C.[object_id]=PTB.major_id left join(select IDXC.[object_id],IDXC.column_id,Sort=case INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,''IsDescending'') WHEN 1 THEN ''DESC'' WHEN 0 THEN ''ASC'' ELSE '''' END,
    PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N''√'' else N'''' END, IndexName = IDX.Name from sys.indexes IDX inner join sys.index_columns IDXC'+' '+
    'on IDX.[object_id]=IDXC.[object_id] and IDX.index_id=IDXC.index_id left join sys.key_constraints KC on IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id'+' '+
    'inner join(select [object_id],Column_id,index_id=min(index_id) from sys.index_columns group by [object_id],Column_id) IDXCUQ on IDXC.[object_id]=IDXCUQ.[object_id]'+' '+
    'and IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id) IDX on C.[object_id]=IDX.[object_id] and C.column_id=IDX.column_id where O.name= ''' + @tablename + ''' ORDER BY O.name,C.column_id'
    print @str
    execute sp_executesql @str
end
GO

<补充>这个获取表中数据结构的T-SQL句子也是不容易写的，也许大家不太能完全看懂(由于本人的写法问题)，如果是不用显示主键外键的问题的话，这个句子可以
缩短4/5，但是因为要显示主外键问题，所以写了挺多判断的，但大家如果要用的话，尽管可以放心，这个T-SQL语句是一定可用的。

 

6、获取指定数据库中全部存储过程

/*存储过程-获取全部存储过程*/
create proc [dbo].[Select_proc](@dataname nvarchar(50))
as 
begin 
    declare @str nvarchar(200)
    set @str = 'use ' + @dataname + ' select SysObjects.name as Procname from Sysobjects where type = ''p'''
    print @str
    execute sp_executesql @str
    select @dataname 
end
GO

7、获取存储过程、触发器、视图脚本

CREATE proc [dbo].[Select_script](@dataname nvarchar(50),@scriptname nvarchar(50))
as
begin    
    declare @str nvarchar(100)
    set @str = 'use ' + @dataname + ' select text from syscomments where id=object_id(N'''+@scriptname+ ''')'
    print @str
    execute sp_executesql @str
    select @dataname
end
GO

 

8、获取指定数据库中全部表

create proc [dbo].[Select_table](@dataname nvarchar(50))
as 
begin 
    declare @str nvarchar(250)
    set @str = 'use ' + @dataname + ' select SysObjects.name as Tablename from Sysobjects where xtype = ''U'''
    print @str
    execute sp_executesql @str
    select @dataname
end    
GO

 

9、获取指定数据库中全部触发器

/*存储过程-获取触发器*/
create proc [dbo].[Select_trigger](@dataname nvarchar(50))
as 
begin 
    declare @str nvarchar(200)
    set @str = 'use ' + @dataname + ' select name as Triname from sys.triggers'
    print @str
    execute sp_executesql @str
    select @dataname
end
GO

 

10、获取指定数据库中全部视图

/*存储过程-获取全部视图*/
create proc [dbo].[Select_view](@dataname nvarchar(50))
as 
begin
    declare @str nvarchar(200)
    set @str = 'use ' + @dataname + ' select name as Viewname from sys.views'
    print @str
    execute sp_executesql @str
    select @dataname
end
GO

 

11、创建定时作业

/*创建作业－参数：作业名、数据库名、备份频率*/
create proc [dbo].[Create_job](@jobname nvarchar(50),@dataname nvarchar(50),@rate nvarchar(5))
as
begin
    /*@rate只能传4、8、16，分别代表日、周、月*/
    declare @str nvarchar(2000)
    set @str = 'declare @jobid uniqueidentifier'+' '+'exec msdb.dbo.sp_add_job'+' '+'@job_name=N'''+@jobname +''',
                @job_id=@jobid output'+' '+'declare @sql nvarchar(400),@dbname sysname'+' '+'select @dbname=DB_NAME(),
                @sql=N''exec Back_data'''''+ @dataname +''''''' '+'exec msdb.dbo.sp_add_jobstep'
                +' '+'@job_id=@jobid,@step_name=N''Step'',@subsystem=''TSQL'',@database_name=N'''+@dataname+''',@command=@sql'
                +' '+'exec msdb.dbo.sp_add_jobschedule'+' '+'@job_id=@jobid,@name=N''Plan'',@freq_type='+@rate+',@freq_interval=1,
                @freq_subday_type=1,@freq_subday_interval=0,@freq_relative_interval=0,@freq_recurrence_factor=1,@active_start_date=Null,
                @active_end_date=99991231,@active_start_time=00000,@active_end_time=235959'
                +' '+'declare @servername sysname set @servername=convert(nvarchar(128),serverproperty(N''local''))'
                +' '+'exec msdb.dbo.sp_add_jobserver @job_id=@jobid,@server_name=@servername'
    print @str
    execute sp_executesql @str 
end
GO

 

12、数据备份（同时数据备份名为动态的）

/*存储过程-数据备份(时间动态名)*/
create proc [dbo].[Back_data](@dataname nvarchar(50))
as
begin
    declare @str nvarchar(250)
    set @str = 'backup database '+@dataname+' to disk = ''F:\数据备份\' + @dataname + '_' +substring(replace(replace(replace(CONVERT(varchar, 
    getdate(), 120 ),'-',''),' ',''),':',''),1,12 )+'.bak''' 
    print @str
    execute sp_executesql @str
    return
end
GO

<补充>需要新建立一个文件夹存放备份文件，像我就是建立如下文件夹—“F:\数据备份”。