SqlServer管理相关

迷南。 2021-09-23 23:06 284阅读 0赞

# 1. docker安装Sqlserver2017 #

docker run  -i -t   --restart=always  -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=ROOT123456.' -p 8433:1433 -v /data/mssql:/var/opt/mssql  --name mssql2017 -d microsoft/mssql-server-linux:2017-latest

在运行docker容器时加 --restart=always 参数保证每次docker服务重启后容器也自动重启，如果已经启动了则可以使用如下命令：docker update --restart=always 进行设置。

# 2. Navicat 新增sqlserver数据库时 #

一般排序规则选择 Chinese\_PRC\_CI\_AS  
恢复模式选择 SIMPLE 节省空间 否则数据存储空间会增加很厉害

用Navicat创建数据库时，记得用sql语句创建：  
把 FILENAME MAXSIZE 那些相关语句去除，否则linux docker版的sqlserver会创建失败。

CREATE DATABASE [数据库名称]
    -- ON
    -- PRIMARY
    -- (
    -- NAME = N'EQMS_Brose_V2',
    -- FILENAME = N'EQMS_Brose_V2.ndf',
    -- SIZE = 8MB,
    -- MAXSIZE = UNLIMITED,
    -- FILEGROWTH = 64MB
    -- )
    -- LOG ON
    -- (
    -- NAME = N'EQMS_Brose_V2_log',
    -- FILENAME = N'EQMS_Brose_V2_log.ldf',
    -- SIZE = 8MB,
    -- MAXSIZE = UNLIMITED,
    -- FILEGROWTH = 64MB
    -- )
    GO
    
    ALTER DATABASE [数据库名称] COLLATE Chinese_PRC_CI_AS
    GO
    
    ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE
    GO
    
    EXEC [EQMS_Brose_V2].dbo.sp_changedbowner N'middolAdmin'

# 3. 查看各个表所占空间大小 #

SELECT
    	t.NAME AS TableName,
    	s.Name AS SchemaName,
    	p.rows AS RowCounts,
    	SUM ( a.total_pages ) * 8 AS TotalSpaceKB,
    	CAST (
    	ROUND((( SUM ( a.total_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 )) AS TotalSpaceMB,
    	SUM ( a.used_pages ) * 8 AS UsedSpaceKB,
    	CAST (
    	ROUND((( SUM ( a.used_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 )) AS UsedSpaceMB,
    	(
    	SUM ( a.total_pages ) - SUM ( a.used_pages )) * 8 AS UnusedSpaceKB,
    	CAST (
    	ROUND((( SUM ( a.total_pages ) - SUM ( a.used_pages )) * 8 ) / 1024.00, 2 ) AS NUMERIC ( 36, 2 )) AS UnusedSpaceMB 
    FROM
    	sys.tables t
    	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
    	LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_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
    	TotalSpaceMB DESC;

# 4. 修改sqlserver数据库字符集排序规则 #

ALTER DATABASE [student] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    ALTER DATABASE [student] COLLATE Chinese_PRC_CI_AS
    
    ALTER DATABASE [student] SET MULTI_USER

# 5. sql查看表结构 #

SELECT  
                    　　表名=d.name,  
                    　　表说明=isnull(f.value,''),  
                    　　字段序号=a.colorder,  
                    　　字段名=a.name,  
                    　　主键=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√' else '' end,  
                    　　类型=b.name,  
                    　　长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),  
                    　　允许空=case when a.isnullable=1 then '√' else '' end,  
                    　　默认值=isnull(e.text,''),  
                    　　字段说明=isnull(g.[value],'')  
                    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.major_id and a.colid=g.minor_id  
                    　　left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0  
                    　　where d.name = 't_myname_table'
                    order by a.id, a.colorder ;

# 6. 杀掉某个数据库下的所有session进程 #

select 'kill '+ cast(spid as varchar(10)) +';' from sysprocesses where dbid=db_id('数据库名称');
    
    -- 然后执行上面查询出的列表
    -- 例如
    kill 32;
    kill 56;

# 7. 查看表的索引信息 #

SELECT * FROM (
    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 ) AS tt  
      WHERE 对象名称 LIKE 'dbo.T_%' 
      AND 索引类型 <> '聚集索引'  ORDER BY 索引名称   ;