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 intdeclare @name varchar(50)declare cursor1 cursor for         --定义游标cursor1select * from table1               --使用游标的对象(跟据需要填入select文)open cursor1                       --打开游标fetch next from cursor1 into @id,@name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中while @@fetch_status=0           --判断是否成功获取数据beginupdate table1 set name=name+‘1‘where id=@id                           --进行相应处理(跟据需要填入SQL文)fetch next from cursor1 into @id,@name  --将游标向下移1行endclose 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)) torder by ColumnIdset @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 UnusedSpaceKBFROM     sys.tables tINNER JOIN     sys.schemas s ON s.schema_id = t.schema_idINNER JOIN          sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN     sys.allocation_units a ON p.partition_id = a.container_idWHERE     t.NAME NOT LIKE 'dt%'    --过滤掉系统表以进行图形化表示    AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255 GROUP BY     t.Name, s.Name, p.RowsORDER BY     UsedSpaceKB,s.Name, t.Name

---

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

/*生成当前数据库的数据字典*/create procedure [dbo].[sp_develop_generatedatedictionary]asSELECT   (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

运行结果如图: