Sql Server 常用操作

ゝ一纸荒年。 2023-10-06 11:48 20阅读 0赞

#### 目录 ####

*  创建、还原数据库
 *  获取本机物理内存大小
 *  修改数据库占用服务大小
 *  查看数据库及数据表结构
 *  查询包含某一字段的所有数据表
 *  使用游标循环
 *  查看数据表索引
 *  数据库报错
 *  数据库可疑
 *  Transact-SQL创建连接服务器
 *  查询数据库所占空间大小

## 创建、还原数据库 ##

-- 判断是否存在指定数据库
    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创建连接服务器 ##

1.  调用存储过程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')