SQL Server 批量处理表

Posted on Edited on In Views: Symbols count in article: 8.6k Reading time ≈ 8 mins.
SQL Server 批量处理表、属性等

背景

由于平台架构的调整，我所负责的项目也迁移到新的框架中了，加上由于历史遗留问题，对表结构有很大影响，为了不影响生产库的数据，需要对测试库和正式库进行结构同步，于是有了这篇文章。

转换表名

如果需要批量转换数据库表名，可以使用以下sql生成的可执行语句

1
2
3
4
5
6
7
8
9
SELECT 'EXEC sp_rename '
+ '''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ''', '
+ '''' + Lower(TABLE_NAME) + '''' as sqlstr
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'dbo'
ORDER BY QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  • Lower(TABLE_NAME) ：将表名转换为小写，如果需要转换成大写可以使用Upper()

如图是生成的可执行SQL语句：

image-20220508231946150

操作的对象是INFORMATION_SCHEMA.TABLES，可以根据需求进行筛选

1
select * from INFORMATION_SCHEMA.TABLES

转换字段

由于生产库以前使用的是pgsql，生成的字段全部为小写，所以下面是生成转换字段的sql

1
2
3
4
5
6
SELECT 'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + 
COLUMN_NAME + ''', ''' +
LOWER(COLUMN_NAME) + ''',''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
'isMSShipped') = 0 AND TABLE_SCHEMA = 'dbo'

image-20220508233159927

这里操作的对象是INFORMATION_SCHEMA.COLUMNS

1
select * from INFORMATION_SCHEMA.COLUMNS

批量删除字段

由于之前迁移pgsql的原因，生成了很多trialxxx的字段，对于这些无用的、历史遗留的也一并解决。

1
2
3
-- 批量操作所有表中的相同字段
select 'ALTER TABLE [dbo].['+tab.name+'] DROP COLUMN '+ col.name + '' as sqlstr from 
sys.tables tab join sys.columns col on tab.object_id = col.object_id and col.name like 'trial%'

这里筛选条件需要根据实际需求调整

image-20220508234429639

批量删除索引

进行结构同步时，由于字段名变化，需要把所有的索引删除。

1
2
3
4
-- 删除所有索引
select 'drop index '+o.name+'.'+i.name+';'
  from sys.indexes i join sys.objects o on  i.object_id=o.object_id
  where o.type<>'S'

这里操作的是sys.indexes，可以根据需求筛选要删除的索引类型等。

1
select * from sys.indexes

批量删除主外键

当数据库表中存在主外键关系时，会阻止我们进行更改操作，所以需要删除主外键关系。

1
2
3
4
5
select 
        'alter table ' + quotename(schema_name(schema_id)) + '.' +
        quotename(object_name(parent_object_id)) +
        ' drop constraint '+quotename(name) + ';'
    from sys.foreign_keys

这里操作的是sys.foreign_keys，可以判断是主键或是外键引用。

1
select * from sys.foreign_keys

删除所有表描述

清空表的描述字段信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select 'EXEC sp_dropextendedproperty

@name = ''MS_Description''

,@level0type = ''schema''

,@level0name = ' + object_schema_name(extended_properties.major_id) + '

,@level1type = ''table''

,@level1name = ' + object_name(extended_properties.major_id)

from sys.extended_properties

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id = 0

and extended_properties.name = 'MS_Description'

删除所有字段描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select  'EXEC sp_dropextendedproperty

@name = ''MS_Description''

,@level0type = ''schema''

,@level0name = ' + object_schema_name(extended_properties.major_id) + '

,@level1type = ''table''

,@level1name = ' + object_name(extended_properties.major_id) + '

,@level2type = ''column''

,@level2name = ' + columns.name  as sqlstr

from sys.extended_properties

join sys.columns

on columns.object_id = extended_properties.major_id

and columns.column_id = extended_properties.minor_id

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id > 0

and extended_properties.name = 'MS_Description'

执行语句

上面的操作都是按条件生成可执行的SQL语句，并没有执行相关操作。

我们使用sys.sp_executesql执行SQL语句。

对于我们查询的结果集，这里提供以下两种方式执行，大家有更好的方法欢迎提出，这里仅供参考，

游标

使用游标逐行执行SQL语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 使用游标处理
declare OneCursor cursor----创建游标
for

-- 放要执行的sql集合
select 'drop index '+o.name+'.'+i.name+';'
  from sys.indexes i join sys.objects o on  i.object_id=o.object_id
  where o.type<>'S' and is_primary_key<>1  

open OneCursor ---打开游标
declare @ItemName nvarchar(max);---声明2个变量用于填充
fetch next from OneCursor into @ItemName ---移动到游标的第一行用于填充
while @@FETCH_STATUS=0 ---若为0，则代表成功执行
begin
print @ItemName
EXECUTE sys.sp_executesql @ItemName
fetch next from OneCursor into @ItemName ----移动到游标的下一行并填充到变量中
end
close OneCursor ---关闭游标
deallocate OneCursor --删除游标

批量执行

执行全部SQL语句

1
2
3
4
5
6
7
8
9
declare @sql nvarchar(max) = (
    select 
        'alter table ' + quotename(schema_name(schema_id)) + '.' +
        quotename(object_name(parent_object_id)) +
        ' drop constraint '+quotename(name) + ';'
    from sys.foreign_keys
    for xml path('')
);
exec sp_executesql @sql;

扩展查询

查询数据库表中的所有字段信息以及描述。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT   
 (case when a.colorder=1 then ddd.value else '' end) as "表名（中文）",--如果表名相同就返回空  
 (case when a.colorder=1 then d.name else '' end) as 表名,--如果表名相同就返回空  
 (case when a.colorder=1 then ddd.value else '' end) as 表说明,--如果表名相同就返回空  
     a.colorder as 字段序号,  
     a.name as 字段名,  
     (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) as 是否自增标识,  
     (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 主键,--查询主键END  
 b.name as 类型,  
 a.length as 占用字节数,  
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as  长度,  
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,  
 (case when a.isnullable=1 then '√'else '' end) as 允许空,  
 isnull(e.text,'') as 默认值, 
 isnull(g.[value],'') AS 字段说明   
 
 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 OUTER JOIN( SELECT major_id, value 
     FROM sys.extended_properties 
     WHERE name='MS_Description' AND minor_id = 0)
    as ddd  ON a.id = ddd.major_id
 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  where d.name like '%'
 order by a.id,a.colorder

查询SQL占用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SELECT TOP 100
[cpu_time],
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 --[command] AS '命令',
 dest.[text] AS 'sql语句',
 DB_NAME([database_id]) AS '数据库名',
 --[blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der
 INNER JOIN [sys].[dm_os_wait_stats] AS dows
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
 --WHERE [session_id]>50
 ORDER BY [cpu_time] DESC;

SELECT TOP 10
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50
ORDER BY [cpu_time] DESC;

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC;

select net_address, hostname,hostprocess,program_name,loginame,login_time from master.dbo.sysprocesses
where dbid = DB_ID('xsteam_datacenter') order by hostprocess;

select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)
from sys.allocation_units a,sys.dm_os_buffer_descriptors b,     sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id      and a.container_id=p.hobt_id     and b.database_id=db_id()
group by p.object_id,p.index_id order by buffer_pages desc

项目迁移数据库做的调整

对EF Core迁移数据库做了部分调整，如下：

我们在EntityFrameworkCore项目中添加扩展类，包含以下扩展方法

引入Riven.EntityFrameworkCore包，熊猫维护的开源包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public static class DbContextExtensions
{
    /// <summary>
    /// 映射规则
    /// </summary>
    /// <param name="modelBuilder"></param>
    /// <returns></returns>
    public static ModelBuilder UseTableMapping(this ModelBuilder modelBuilder)
    {
        var verifyingEntityType = new Func<IMutableEntityType, bool>((e) => EntityHelper.IsEntity(e.ClrType));

        return modelBuilder
            .TableMappingTo(verifyingEntityType, (x) => x.ToLower()) // 将表名和字段映射为小写
            .MapDiscriminators(verifyingEntityType);
    }
}

并在DbContext添加对扩展方法的调用

1
2
3
4
public override void ModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.UseTableMapping();
}

然后我们重新迁移到一个新的数据库，使用以下迁移命令

1
2
Add-Migration initdb
Update-DataBase

需要删除之前生成的迁移文件

Related Posts