目录
创建、还原数据库
-- 判断是否存在指定数据库
IF EXISTS(SELECT 1 FROM master..sysdatabases WHERE name = 'dbName')
BEGIN
CREATE DATABASE [dbName]
ON
PRIMARY
(
NAME = N'dbName',
FILENAME = N'D:\Program Files\ListDBData\dbName.ndf',
SIZE = 2304KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB
)
LOG ON
(
NAME = N'dbName_log',
FILENAME = N'D:\Program Files\ListDBData\dbName_log.ldf',
SIZE = 768KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
)
END
GO
-- 还原数据库
RESTORE DATABASE [dbName]
FROM
DISK = N'D:\Program Files\dataBackUp\dbName.bak'
WITH
FILE = 1,
MOVE N'dbName' TO 'D:\Program Files\ListDBData\dbName.ndf',
MOVE N'dbName_log' TO 'D:\Program Files\ListDBData\dbName_log.ldf',
REPLACE,
RECOVERY,
STATS = 5;
GO
获取本机物理内存大小
SELECT
CEILING( total_physical_memory_kb * 1.0 / 1024 / 1024 ) AS [Physical Memory Size],
CAST (
available_physical_memory_kb * 1.0 / 1024 / 1024 AS DECIMAL ( 8, 4 )) AS [Unused Physical Memory],
CAST ((
total_physical_memory_kb - available_physical_memory_kb
) * 1.0 / 1024 / 1024 AS DECIMAL ( 8, 4 )) AS [Used Physical Memory],
CAST (
system_cache_kb * 1.0 / 1024 / 1024 AS DECIMAL ( 8, 4 )) AS [System Cache Size]
FROM
sys.dm_os_sys_memory
修改数据库占用服务大小
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'min server memory', 0;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2048;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
通过该方法修改数据库占用内存可不用重启数据库服务
查看数据库及数据表结构
-- 获取所有数据库名:
SELECT name FROM master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs' )
-- 获取某数据库的所有表：
SELECT name FROM dbName..sysobjects Where xtype='U' ORDER BY name
-- 获取所有表名
-- XType='U':表示所有用户表;
-- XType='S':表示所有系统表;
-- 查看数据表的主键
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='tableName'
-- 获取表的主键
EXEC sp_pkeys 'tableName'
-- 获取表的外键
EXEC sp_fkeys 'tableName'
-- 查看数据表的字段及属性
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('tableName')
查询包含某一字段的所有数据表
select object_id(id) from syscolumns where name = 'columnName' and id in(select id from sysobjects where xtype = 'u')
使用游标循环
BEGIN TRAN a
declare @Error int = 0;
-- 定义并声明一个游标
DECLARE modifyColumns CURSOR FOR
SELECT object_name( id ) FROM syscolumns WHERE ( name LIKE 'columnName' ) AND id IN ( SELECT id FROM sysobjects WHERE xtype = 'u' )
-- 打开游标
OPEN modifyColumns
DECLARE @tableName varchar(64)
-- 开始循环游标变量
FETCH NEXT FROM modifyColumns INTO @tableName
-- 返回游标状态，为0时结束
WHILE @@fetch_status = 0
BEGIN
EXEC('
EXEC sp_rename ''' + @tableName + '.columnName'', ''columnNameNew'', ''COLUMN''
')
-- 记录每次执行sql是否正确，0为正确
set @Error = @Error + @@ERROR
-- 为下一次循环赋值
FETCH NEXT FROM modifyColumns INTO @tableName
END
if @Error = 0
commit tran a -- 若正确提交事务
else
rollback tran a -- 若有误则回滚事务
-- 关闭游标
CLOSE modifyColumns
-- 释放游标
DEALLOCATE modifyColumns
查看数据表索引
SELECT CASE
WHEN t.[type] = 'U' THEN
'表'
WHEN t.[type] = 'V' THEN
'视图'
END AS '类型',
SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
i.[name] AS 索引名称,
SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
CASE
WHEN i.[type] = 1 THEN
'聚集索引'
WHEN i.[type] = 2 THEN
'非聚集索引'
WHEN i.[type] = 3 THEN
'XML索引'
WHEN i.[type] = 4 THEN
'空间索引'
WHEN i.[type] = 5 THEN
'聚簇列存储索引'
WHEN i.[type] = 6 THEN
'非聚集列存储索引'
WHEN i.[type] = 7 THEN
'非聚集哈希索引'
END AS '索引类型',
CASE
WHEN i.is_unique = 1 THEN
'唯一'
ELSE
'不唯一'
END AS '索引是否唯一'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY
(
SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
AND index_id > 0
ORDER BY t.[name];
数据库报错
报错信息：
[SQL Server]SQL Server 检测到基于一致性的逻辑 I/O 错误 校验和不正确(应为: 0x140e2a6f，但实际为: 0x164d3c00)。在文件 ‘xxxxxxxxxxxxx.MDF’ 中、偏移量为 0x000002ec20a000 的位置对数据库 ID 15 中的页 (1:1532165) 执行 读取 期间，发生了该错误。SQL Server 错误日志或系统事件日志中的其他消息可能提供了更详细信息。这是一个威胁数据库完整性的严重错误条件，必须立即纠正。请执行完整的数据库一致性检查(DBCC CHECKDB)。此错误可以由许多因素导致；有关详细信息，请参阅 SQL Server 联机丛书。 (824)
处理脚本
use master
go
ALTER DATABASE dbname SET SINGLE_USER
go
DBCC CHECKDB ('dbname', repair_allow_data_loss) with NO_INFOMSGS
go
ALTER DATABASE dbname SET MULTI_USER
go
在恢复文件过程中可能会丢失一些数据，如果执行长时间无反应可重启数据库再尝试，也有可能跟数据库文件过大有关，可以清理数据再做尝试。
数据库可疑
ALTER DATABASE dbName SET EMERGENCY
ALTER DATABASE dbName SET SINGLE_USER
/**
修复数据库日志重新生成，此命令检查的分配，结构，逻辑完整性和所有数据库中的对象错误。当您指定“REPAIR_ALLOW_DATA_LOSS”作为DBCC CHECKDB命令参数，该程序将检查和修复报告的错误。但是，这些修复可能会导致一些数据丢失
*/
DBCC CheckDB (dbName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbName SET MULTI_USER
Transact-SQL创建连接服务器
- 调用存储过程sp_addlinkedserver sp_addlinkedlogin创建连接服务器
- sp_addlinkedserver
sp_addlinkedserver
[ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
使用sp_addlinkedserver创建链接服务器后，可对该服务器运行分布式查询。 如果链接服务器定义为 SQL Server实例，则可执行远程存储过程。
[@server =] = ‘server’ 要创建的连接服务器的名称
[@srvproduct= ] ‘product_name’
要添加为链接服务器的 OLE DB 数据源的产品名称。 product_name 的数据类型为 nvarchar(128)，默认值为 NULL。
[ @provider= ] ‘provider_name’
与此数据源对应的 OLE DB 访问接口的唯一编程标识符 (PROGID)。 对于当前计算机上安装的指定 OLE DB 提供程序， provider_name必须是唯一的。 provider_name为nvarchar （128），默认值为 NULL;
[ @datasrc = ]* ‘data_source’ *
由 OLE DB 访问接口解释的数据源的名称。 data_source为nvarchar （ 4000 ）。 data_source作为 DBPROP_INIT_DATASOURCE 属性传递以初始化 OLE DB 提供程序。
[ @location = ]* ‘位置’ *
由 OLE DB 访问接口解释的数据库的位置。 location的值为nvarchar （ 4000 ），默认值为 NULL。 location作为 DBPROP_INIT_LOCATION 属性传递以初始化 OLE DB 提供程序。
[ @provstr = ]* ‘provider_string’ *
OLE DB 访问接口特定的连接字符串，它可标识唯一的数据源。 provider_string为nvarchar （ 4000 ），默认值为 NULL。 provstr传递给 IDataInitialize，或设置为 DBPROP_INIT_PROVIDERSTRING 属性以初始化 OLE DB 提供程序。
- sp_addlinkedsrvlogin
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] { 'TRUE' | 'FALSE' | NULL } ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
[ @rmtsrvname = ] ‘rmtsrvname’
应用登录映射的链接服务器的名称。 rmtsrvname的值为sysname，无默认值。
[ @useself = ] { ‘TRUE’ | ‘FALSE’ | NULL }’
确定是通过模拟本地登录名连接到rmtsrvname ，还是显式提交登录名和密码。 数据类型为varchar （ 8 ），默认值为 TRUE。
如果值为 TRUE，则指定登录名使用其自己的凭据连接到rmtsrvname，并忽略rmtuser和rmtpassword参数。 FALSE 指定rmtuser和rmtpassword参数用于连接到指定locallogin的rmtsrvname 。 如果将rmtuser和RMTPASSWORD设置为 NULL，则不会使用登录名或密码连接到链接服务器。
[ @locallogin = ] ‘locallogin’
本地服务器上的登录。 locallogin的值为sysname，默认值为 NULL。 NULL 指定此条目适用于连接到rmtsrvname的所有本地登录名。 如果不为 NULL，则locallogin可以SQL Server为登录名或 Windows 登录名。 对于 Windows 登录来说，必须以直接的方式或通过已被授权访问的 Windows 组成员身份授予其访问 SQL Server 的权限。
[ @rmtuser = ] ‘rmtuser’
当为 FALSE 时@useself ，用于连接到rmtsrvname的远程登录名。 当远程服务器是不使用 Windows 身份SQL Server验证的实例时， rmtuser是一个SQL Server登录名。 rmtuser的值为sysname，默认值为 NULL。
[ @rmtpassword = ] ‘rmtpassword’
与rmtuser关联的密码。 rmtpassword的值为sysname，默认值为 NULL。
例：
-- 创建连接服务器
exec sp_addlinkedserver 'LINK_SQL', '', 'SQLOLEDB', '127.0.0.1,2433'
exec sp_addlinkedsrvlogin 'LINK_SQL', 'FALSE', NULL, 'sa', '123456'
-- 通过连接服务器查询
select * from [LINK_SQL].[dbName].[dbo].[tableName]
-- 删除连接服务器
exec sp_dropserver 'LINK_SQL', 'DROPLOGINS'
查询数据库所占空间大小
SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [LogicalName],[Physical_Name] AS[Physical Name],((size *8) / 1024) AS [Size(MB)],[differential_base_time] AS[Differential Base Time]
FROM sys.master_files
WHERE DB_NAME(database_id) IN('dbName')
142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
