select DATA_TYPE from information_schema.columns group by DATA_TYPE |
bit datetime datetime2 int ntext nvarchar uniqueidentifier varbinary |
select distinct TABLE_NAME from information_schema.columns order by TABLE_NAME |
SELECT * FROM information_schema.columns WHERE table_catalog = '資料庫名稱' AND table_name NOT LIKE '%zDel%' AND table_name NOT LIKE '%aspnet%' AND table_name NOT LIKE '%__MigrationHistory%' AND table_name NOT LIKE 'View%' AND table_name <> '%ELMAH_Error%' AND table_name <> 'Sessions' AND table_name <> 'sysdiagrams' AND is_nullable = 'YES' |
-- 解決 "不允許對系統目錄的特定更新。" sp_configure 'allow updates',1 RECONFIGURE WITH override go -- 修正欄位格式 ntext, nvarchar 預設值為 '' UPDATE information_schema.columns SET column_default = '' WHERE table_catalog = '資料庫名稱' AND table_name NOT LIKE '%zDel%' AND table_name NOT LIKE '%aspnet%' AND table_name NOT LIKE '%__MigrationHistory%' AND table_name NOT LIKE 'View%' AND table_name <> '%ELMAH_Error%' AND table_name <> 'Sessions' AND table_name <> 'sysdiagrams' AND is_nullable = 'YES' AND ( data_type = 'ntext' OR data_type = 'nvarchar' ); -- 修正欄位格式 datetime, datetime2 預設值為 datetime() UPDATE information_schema.columns SET column_default = 'datetime()' WHERE table_catalog = '資料庫名稱' AND table_name NOT LIKE '%zDel%' AND table_name NOT LIKE '%aspnet%' AND table_name NOT LIKE '%__MigrationHistory%' AND table_name NOT LIKE 'View%' AND table_name <> '%ELMAH_Error%' AND table_name <> 'Sessions' AND table_name <> 'sysdiagrams' AND is_nullable = 'YES' AND ( data_type = 'datetime' OR data_type = 'datetime2' ); -- 修正欄位格式 int 預設值為 0 UPDATE information_schema.columns SET column_default = 0 WHERE table_catalog = '資料庫名稱' AND table_name NOT LIKE '%zDel%' AND table_name NOT LIKE '%aspnet%' AND table_name NOT LIKE '%__MigrationHistory%' AND table_name NOT LIKE 'View%' AND table_name <> '%ELMAH_Error%' AND table_name <> 'Sessions' AND table_name <> 'sysdiagrams' AND is_nullable = 'YES' AND ( data_type = 'int' ); -- 修正欄位格式 bit, 欄位名稱 IsValid 預設值為 1 UPDATE information_schema.columns SET column_default = 1 WHERE table_catalog = '資料庫名稱' AND table_name NOT LIKE '%zDel%' AND table_name NOT LIKE '%aspnet%' AND table_name NOT LIKE '%__MigrationHistory%' AND table_name NOT LIKE 'View%' AND table_name <> '%ELMAH_Error%' AND table_name <> 'Sessions' AND table_name <> 'sysdiagrams' AND is_nullable = 'YES' AND ( data_type = 'ntext' OR data_type = 'nvarchar' ) AND ( column_name = 'IsValid' ); -- 修正欄位格式 bit, 欄位名稱不是 IsValid 預設值為 0 UPDATE information_schema.columns SET column_default = 0 WHERE table_catalog = '資料庫名稱' AND table_name NOT LIKE '%zDel%' AND table_name NOT LIKE '%aspnet%' AND table_name NOT LIKE '%__MigrationHistory%' AND table_name NOT LIKE 'View%' AND table_name <> '%ELMAH_Error%' AND table_name <> 'Sessions' AND table_name <> 'sysdiagrams' AND is_nullable = 'YES' AND ( data_type = 'ntext' OR data_type = 'nvarchar' ) AND ( column_name <> 'IsValid' ); go -- 復原 "不允許對系統目錄的特定更新。" sp_configure 'allow updates',0 RECONFIGURE WITH override go |
use pms go SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT ('''') FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' ) union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (''getdate()'') FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' ) union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='int') union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (1) FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='bit') and ( COLUMN_NAME='IsValid') union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='bit') and ( COLUMN_NAME<>'IsValid') |
use pms ; -- 若 ntext, nvarchar 欄位允許 null ，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']='''' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' ) group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 datetime, datetime2 欄位允許 null ，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0-1-1'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' ) group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 int 欄位允許 null ，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='int') group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''1'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='bit') and ( COLUMN_NAME='IsValid') group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='bit') and ( COLUMN_NAME<>'IsValid') group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE |
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace WebApplication1 { public partial class WebForm1 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { var connectionString = "Data Source=10.3.99.192;Initial Catalog=pms;User ID=sa;Password=P@ssw0rd"; using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand( @"select DISTINCT [TABLE_NAME] FROM information_schema.columns WHERE TABLE_CATALOG = 'pms' and[TABLE_NAME] not like '%zDel%' and[TABLE_NAME] not like '%aspnet%' and[TABLE_NAME] not like '%__MigrationHistory%' and[TABLE_NAME] not like 'View%' and[TABLE_NAME] <> '%ELMAH_Error%' and[TABLE_NAME] <> 'Sessions' and[TABLE_NAME] <> 'sysdiagrams' ; ", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); string str = ""; if (reader.HasRows) { while (reader.Read()) { str = @" select 'ALTER TABLE dbo." + reader[0].ToString() + @" ALTER COLUMN ' + QUOTENAME(c.name) + ' ' + t.name + case when t.name in ('nvarchar','nchar') and c.max_length <>-1 then '('+ cast(c.max_length/2 as nvarchar) +')' when t.name in ('nvarchar','nchar') and c.max_length =-1 then '(MAX)' when t.name in ('varchar','binary', 'char', 'varbinary') then '('+ cast(c.max_length as nvarchar) +')' else '' end + ' NOT NULL'from sys.columns c join sys.types t on c.system_type_id = t.system_type_id where object_id = object_id('dbo." + reader[0].ToString() + @"') and t.name <> 'sysname'"; //order by column_id"; if (Literal1.Text == "") Literal1.Text = "(" + str + ")"; else Literal1.Text = Literal1.Text + "<br /><br />union<br /><br />" + "(" + str + ")"; } } else { //Console.WriteLine("No rows found."); } reader.Close(); } } } } |
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="2. Generate SQL - Set NULL field Format the DEFAULT Value" OnClick="Button1_Click" /><br /> <br /> <asp:Button ID="Button2" runat="server" Text="3. Generate SQL - Give NULL field a Value" OnClick="Button2_Click" /><br /> <br /> <asp:Button ID="Button3" runat="server" Text="4. Generate SQL - Change NULL Format to NOT NULL Format" OnClick="Button3_Click" /><br /> <br /> <asp:Literal ID="Literal1" runat="server"></asp:Literal><br /> <asp:Literal ID="Literal2" runat="server" Visible="false"></asp:Literal><br /> </div> </form> </body> </html> |
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace WebApplication1 { public partial class WebForm1 : System.Web.UI.Page { const string connectionString = "Data Source=.;Initial Catalog=pms;User ID=sa;Password=P@ssw0rd"; protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { Literal1.Text = ""; using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand(@" SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT ('''') FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and COLUMN_DEFAULT is NULL and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' ) union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (getdate()) FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and COLUMN_DEFAULT is NULL and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' ) union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and COLUMN_DEFAULT is NULL and ( DATA_TYPE='int') union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (1) FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and COLUMN_DEFAULT is NULL and ( DATA_TYPE='bit') and ( COLUMN_NAME='IsValid') union SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and COLUMN_DEFAULT is NULL and ( DATA_TYPE='bit') and ( COLUMN_NAME<>'IsValid') ;", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); string str = ""; if (reader.HasRows) { while (reader.Read()) { str = reader[0].ToString(); if (Literal1.Text == "") { Literal1.Text = "use pms;" + "<br />GO<br /><br />"; Literal1.Text = Literal1.Text + "delete InformationSystem where todoid is null;" + "<br />GO<br /><br />"; // 修正 Bug } else Literal1.Text = Literal1.Text + str + "<br />GO<br /><br />"; } } else { //Console.WriteLine("No rows found."); } reader.Close(); connection.Close(); } } protected void Button2_Click(object sender, EventArgs e) { Literal1.Text = ""; using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand(@" -- 若 ntext, nvarchar 欄位允許 null ，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']='''' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' ) group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 datetime, datetime2 欄位允許 null ，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0-1-1'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' ) group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 int 欄位允許 null ，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='int') group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''1'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='bit') and ( COLUMN_NAME='IsValid') group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE union -- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容 SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;' FROM information_schema.columns WHERE TABLE_CATALOG='pms' and [TABLE_NAME] not like '%zDel%' and [TABLE_NAME] not like '%aspnet%' and [TABLE_NAME] not like '%__MigrationHistory%' and [TABLE_NAME] not like 'View%' and [TABLE_NAME] <> '%ELMAH_Error%' and [TABLE_NAME] <> 'Sessions' and [TABLE_NAME] <> 'sysdiagrams' and IS_NULLABLE ='YES' and ( DATA_TYPE='bit') and ( COLUMN_NAME<>'IsValid') group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE --order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE ;", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); string str = ""; if (reader.HasRows) { while (reader.Read()) { str = reader[0].ToString(); if (Literal1.Text == "") { Literal1.Text = "use pms;" + "<br />GO<br /><br />"; } else Literal1.Text = Literal1.Text + str + "<br />GO<br /><br />"; } } else { //Console.WriteLine("No rows found."); } reader.Close(); connection.Close(); } } protected void Button3_Click(object sender, EventArgs e) { Literal1.Text = ""; Literal2.Text = ""; using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand( @"select DISTINCT [TABLE_NAME] FROM information_schema.columns WHERE TABLE_CATALOG = 'pms' and[TABLE_NAME] not like '%zDel%' and[TABLE_NAME] not like '%aspnet%' and[TABLE_NAME] not like '%__MigrationHistory%' and[TABLE_NAME] not like 'View%' and[TABLE_NAME] <> '%ELMAH_Error%' and[TABLE_NAME] <> 'Sessions' and[TABLE_NAME] <> 'sysdiagrams' ; ", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); string str = ""; if (reader.HasRows) { while (reader.Read()) { str = @" select 'ALTER TABLE [" + reader[0].ToString() + @"] ALTER COLUMN ' + QUOTENAME(c.name) + ' ' + t.name + case when t.name in ('nvarchar','nchar') and c.max_length <>-1 then '('+ cast(c.max_length/2 as nvarchar) +')' when t.name in ('nvarchar','nchar') and c.max_length =-1 then '(MAX)' when t.name in ('varchar','binary', 'char', 'varbinary') then '('+ cast(c.max_length as nvarchar) +')' else '' end + ' NOT NULL; ' from sys.columns c join sys.types t on c.system_type_id = t.system_type_id where object_id = object_id('dbo." + reader[0].ToString() + @"') and t.name <> 'sysname' and t.is_nullable =1 "; //order by column_id"; if (Literal1.Text == "") { Literal1.Text = "use pms;" + "<br />GO<br /><br />"; Literal1.Text = "(" + str + ")"; Literal2.Text = "use pms;\nGO\n"; Literal2.Text = "(" + str + ")\n"; } else Literal1.Text = Literal1.Text + "<br /><br />union<br /><br />" + "(" + str + ")"; Literal2.Text = Literal2.Text + "\nunion\n" + "(" + str + ")\n"; } } else { //Console.WriteLine("No rows found."); } reader.Close(); } // Literal1.Text = ""; using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand(Literal2.Text,connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); string str = ""; if (reader.HasRows) { while (reader.Read()) { str = reader[0].ToString(); if (Literal1.Text == "") { Literal1.Text = "use pms;" + "<br />GO<br /><br />"; } else Literal1.Text = Literal1.Text + str + "<br /><br />GO<br /><br />"; } } else { //Console.WriteLine("No rows found."); } reader.Close(); connection.Close(); } } } } |
USE [TestDB] GO INSERT INTO [dbo].[TestTable] ([myname] ,[addr] ,[createtime]) VALUES ('name1','addr1','1753-1-1'); GO select * from TestTable; |
