备份所有指定的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
select *
from table1
open cursor1
fetch next
from cursor1
into @
id,@
name
while @@fetch_status=
0
begin
update table1
set name=
name+‘
1‘
where id=@
id
fetch next
from cursor1
into @
id,@
name
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
运行结果如图:
