如何批量给Sqlserver数据库表中的如：创建时间设置为自动填充约束

小半 • 2022年12月21日下午9:49 • .NET • 阅读 244

批量给数据库表中的如：创建时间设置为自动填充约束

1:小知识补充

//———–sqlserver

1.获取所有数据库名: 
SELECT Name FROM Master..SysDatabases ORDER BY Name 
2.获取所有表名: 
SELECT Name FROM DatabaseName..SysObjects Where XType='U' ORDER BY Name 
XType='U':表示所有用户表; 
XType='S':表示所有系统表; 
3.获取所有字段名: 
SELECT Name FROM SysColumns WHERE id=Object_Id('TableName')

2：代码实现

//----------批量给数据库的表时间字段设置为自动时间
 public async Task<ApiResultDto> InitAddAutoDateTime()
        {
            /*
             SELECT Name FROM Scenic..SysObjects Where XType='U' ORDER BY Name ;
             SELECT isnull( Name,'0')cname FROM SysColumns WHERE id=Object_Id('gsm_send_wx') and name like '%update_date%';
             ALTER TABLE [dbo].[bct_dept_print] ADD  CONSTRAINT [DF_bct_dept_print_update_date]  DEFAULT (getdate()) FOR [update_date]
             */

            List<string> listTableName = await DbScoped.Sugar.Ado.SqlQueryAsync<string>("SELECT Name FROM Scenic..SysObjects Where XType='U' ORDER BY Name");
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < listTableName.Count; i++)
            {
                string tname = listTableName[i];
                string sqlColumunList = $"SELECT isnull( Name,'0')cname FROM SysColumns WHERE id=Object_Id('{tname}') and lower(name)  like '%update_date%' ";
                string gname= await DbScoped.Sugar.Ado.SqlQuerySingleAsync<string>(sqlColumunList);
                if (!string.IsNullOrEmpty(gname))
                {
                  
                    string addTimeSql = $" ALTER TABLE [dbo].[{tname}] ADD  CONSTRAINT [DF_{tname}_update_date]  DEFAULT (getdate()) FOR [update_date]";
                    try
                    {
                       int count= await DbScoped.Sugar.Ado.ExecuteCommandAsync(addTimeSql);
                        if (count>0)
                            sb.Append(tname+",");
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
            }
            return ApiResultDto.ToResultSuccess(data:sb.ToString());
        }