MSSQL实用脚本

备份所有指定的MSSQL数据库

DECLARE @name VARCHAR(50) -- 数据库名
DECLARE @path VARCHAR(256) -- 备份文件路径
DECLARE @fileName VARCHAR(256) -- 备份文件名  
DECLARE @fileDate VARCHAR(20) -- 用来做文件名的


SET @path = 'D:\backup\'


SELECT @fileDate = CONVERT(VARCHAR(8),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  

SELECT name 
FROM master.dbo.sysdatabases                                    
WHERE name NOT IN ('master','model','msdb','tempdb','mydb1','mydb2','mydb3','mydb4') 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   --判断是否成功获取数据
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.bak'  
       BACKUP DATABASE @name TO DISK = @fileName   

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

SQL Cursor 基本用法

declare @id int
declare @name varchar(50)
declare cursor1 cursor for         --定义游标cursor1
select * from table1               --使用游标的对象(跟据需要填入select文)
open cursor1                       --打开游标

fetch next from cursor1 into @id,@name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中

while @@fetch_status=0           --判断是否成功获取数据
begin
update table1 set name=name+‘1‘
where id=@id                           --进行相应处理(跟据需要填入SQL文)

fetch next from cursor1 into @id,@name  --将游标向下移1行
end

close cursor1                   --关闭游标
deallocate cursor1

游标一般格式：

DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,… FROM 表名 WHERE …
OPEN 游标名称
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,…
WHILE @@FETCH_STATUS=0
BEGIN
SQL语句执行过程… …
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,…
END
CLOSE 游标名称
DEALLOCATE 游标名称 (删除游标)

注意： 用两次 “FETCH NEXT FROM ” 是因为，第一次只是用来判断 @@FETCH_STATUS的，后面一次 “FETCH NEXT FROM ” 才是 loop用到的！也就是后面一次 是在 BEGIN END之间，是被反复执行的。。每次读取一行！

---

数据库表直接生成类

declare @TableName sysname = 'Employees'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result

---

恢复数据库

RESTORE DATABASE MyDb FROM DISK = 'C:\World.BAK'
GO

---

获取数据表大小

SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'    --过滤掉系统表以进行图形化表示
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceKB,s.Name, t.Name

---

生成一个数据库的数据字典

/*
生成当前数据库的数据字典
*/
create procedure [dbo].[sp_develop_generatedatedictionary]
as

SELECT  
 (case when a.colorder=1 then d.name else '' end) as N'表名', 
 a.colorder as N'字段序号', 
 a.name as N'字段名', 
 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) as N'标识', 
 (
     case when 
     (
         SELECT count(*) FROM sysobjects 
         WHERE (name in 
                  (SELECT name FROM sysindexes 
                   WHERE id = a.id AND 
                   (indid in 
                           (
                            SELECT indid FROM sysindexkeys WHERE id = a.id AND 
                            colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))
                           )
                   )
                  )
               ) AND (xtype = 'PK')
     )>0 then '√' else '' end
 ) as N'主键', 
 b.name N'类型', 
 a.length N'占用字节数', 
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', 
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', 
 (case when a.isnullable=1 then '√'else '' end) N'允许空', 
 isnull(e.text,'') N'默认值', 
 isnull(g.[value],'') AS N'字段说明' 
 --into ##tx 
  FROM  syscolumns  a left join systypes b  
 on  a.xtype=b.xusertype 
 inner join sysobjects d  
 on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties' 
 left join syscomments e 
 on a.cdefault=e.id 
 left join  sys.extended_properties g on a.id=g.class and a.colid=g.minor_id
 order by object_name(a.id),a.colorder

运行结果如图: