一些sql整理

1、通过sql语言查询表结构：(MSSQL2000中有效)

作用：可以快速生成数据词典。

SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名,   
      t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空,   
      CAST(m.[value] AS Varchar(100)) AS 说明  
FROM dbo.syscolumns c INNER JOIN 
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND   
      o.name <> 'dtproperties' INNER JOIN 
      dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN 
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder  
ORDER BY o.name, c.colid 

说明：【sysproperties】表 原来【SQL2000】中的表【sysproperties】在【SQL2005】中已经不存在了，被是表【extended_properties】取代了

所以在SQL2005中改成：

SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名,   
      t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空,   
      CAST(m.[value] AS Varchar(100)) AS 说明  
FROM dbo.syscolumns c INNER JOIN 
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND   
      o.name <> 'dtproperties' INNER JOIN 
      dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN 
      sys.extended_properties m ON m.major_id = o.id AND m.minor_id = c.colorder  
ORDER BY o.name, c.colid  

2、通过作业定制定期数据库备份

定期备份数据库，减少人工操作，但是缺点是随着时间的增长，不断生成备份文件，造成文件容量不断增大，需要定期删除早期的不需要的备份。可以定制作业定期删除规定时间内的备份文件。参考之前整理的资料（数据库日常维护常用的脚本部分收录）

根据需要修改备份存储路径、数据库名称、备份名称；作用备份语句：

declare @filename nvarchar(100);  
set @filename='E:\BackUP_data_bak\databaseName_data\databaseName_backup_'
+convert(char(10),getdate(),112)+'.bak' print @filename;  
backup database [DatabaseDB] 
to disk=@filename with noinit,nounload,name=N'BackUP_databaseName',  
noskip,stats=10,noformat;     /*DatabaseDB:要备份的数据库名称;BackUPDatabase:备份名称*/ 

 3、函数（工作中的需求）

根据给出的字符串得出相应的权重，给出的字符串如：'S41J11G41S21'

执行代码：

select dbo.fun_GetPrizeValue('S41J11G41S21') 

函数代码：

create function [dbo].[fun_GetPrizeValue](@str varchar(15))  
returns float 
as   
begin 
declare @num int,@i int,@value float,@sum float,@m int 
set @i=1  
set @m=0  
set @sum=0  
if(@str='') set @num=0  
else select @num=len(@str)  
while @i<=(@num/3)  
begin 
select @value=case when substring(@str,@i+@m,2)= 'G2' then 9  
when substring(@str,@i+@m,2)='G3' then 0.95  
when substring(@str,@i+@m,2)='G4' then 0.8  
when substring(@str,@i+@m,2)='S2' then 0.75  
when substring(@str,@i+@m,2)='J1' then 0.7  
when substring(@str,@i+@m,2)='G1' then 95  
when substring(@str,@i+@m,2)='S4' then 0.6  
else 0 end 
set @sum=@value*convert(int,substring(@str,@i+2+@m,1))+@sum 
set @i=@i+1  
set @m=@m+2  
end 
return @sum 
end 

 4、递归查询

当遇到一个树状结构存储的表时，即，一个表中存在两个字段：ID，ParentID时，通过给出一个ID，获得该ID的所有子信息，通过创建存储过程，并调用存储过程实现。可根据需要做相应的修改。

存储过程：

create procedure [dbo].[sp_search](@id int)  
as 
declare @level int,@cur int 
create table #stack  
(depth_level int,  
tb1_id int 
)  
create table #tmp  
(seq_no int identity(1,1),  
org_level int not null,  
tb1_id int not null 
)  
select @level=1  
select @cur=@id  
insert into #stack(depth_level,tb1_id) select @level,tableNameID from tableName where tablenameID=@id  
while (@level>0)  
begin 
if exists (select * from #stack where depth_level=@level)  
begin 
set rowcount 1  
select @cur=tb1_id from #stack where depth_level=@level 
set rowcount 0  
insert into #tmp(org_level,tb1_ID)select @level,@cur  
delete from #stack where depth_level=@level and tb1_id=@cur  
insert into #stack select @level+1,tableNameID from tableName where   
tableNameFatherID=@cur and tableNameFatherID<>tableNameID  
if @@rowcount>0  
select @level=@level+1  
end   
else 
begin 
select @level=@level-1  
end 
end 
/*select * from #tmp*/  
select * from tableName where tableNameID in (select tb1_id from #tmp) 

执行代码：

exec dbo.sp_search 1 

 5、创建数据库SQL语言

create database DatabaseDB  
on primary 
(  
name='DatabaseDB_Data',--主数据文件的逻辑名  
filename='D:\database\DatabaseDB_data.mdf',--主数据文件的物理名  
size=10MB,--初始大小  
filegrowth=10%--增长率  
)  
log on 
(  
name='DatabaseDB_Log',--日志文件的逻辑名  
fileName='D:\database\DatabaseDB_data.ldf',--日志文件的物理名  
size=1MB,  
maxsize=20MB,--最大大小  
filegrowth=10%  
)