C# SQL: Query to find a specific column in each tables and all databases.

Scenario:

In the CustomerID there is some Customer Account information which we do not want. So we have to find the CustomerID column that is used almost in all databases and all the tables and replace the CustomerID value with CustomerIDPseudonymisedNumber.

Plan:

  1. SQL Update query: Write a query which will look CustomerID in a table if CustomerID column exist then it will return information: DatabaseName, SchemaName, TableName, ColumnName. It will also return a dynamically build sql update query. We want to to store the result the query into a table
  2. ETL Toool: Now we want to create an ETL tool which will get the update query from the table and execute it. We also want to log the result of the execute query if it has successfully completed the query or failed. If failed also log the error message

 

Steps 1 – SQL Update query:

USE SUS_SEM;
GO

DECLARE
@RETURN_VALUE INT
,@command1 nvarchar(MAX)

IF OBJECT_ID(‘tempdb..#temp’, ‘U’) IS NOT NULL DROP TABLE tempdb..#temp
IF OBJECT_ID(‘tempdb..#temp2’, ‘U’) IS NOT NULL DROP TABLE tempdb..#temp2

CREATE TABLE #temp
(
ServerName nvarchar(100)
,Table_Catalog nvarchar(256)
,Table_Schema nvarchar(256)
,Table_Name SYSNAME
,Column_Name SYSNAME
,Row_Count int
,Table_Size_MB decimal(10,2)
,Table_Size_GB decimal(10,2)
)

SET @command1 = ‘
USE [?];
DECLARE @dbName nvarchar(100);
SET @dbName=”?”;
INSERT INTO #temp
SELECT
@@SERVERNAME AS ServerName
,@dbName AS DatabaseName
,s.Name AS SchemaName
,t.NAME AS TableName
,c.name AS ColumnName
,p.rows AS RowCounts
,CONVERT(decimal(10, 2), (SUM(a.used_pages) * 8))/(1024) AS SizeInMB
,CONVERT(decimal(10, 2), (SUM(a.used_pages) * 8))/(1024*1024) AS SizeInGB
FROM
sys.tables t
INNER JOIN sys.columns AS c
ON t.object_id=c.object_id
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
AND @dbName NOT IN (”RefDB”, ”master”, ”msdb”, ”temp”, ”model”, ”distribution”)
AND @dbName IN (””)
AND t.NAME NOT IN (””)
–AND t.NAME IN(””)
AND t.NAME IN(””)
AND c.name IN ( ”Number”, ”No”, ”Num”, ” Number”, ”_Number”, ”_No”, ”_Num”, ” No#”, ”xNumber”)
AND p.rows>0
GROUP BY
t.Name, s.Name, p.Rows, c.name
ORDER BY
t.Name’

/*EXECUTE master.sys.sp_MSforeachdb ‘USE [?]; EXEC sp_spaceused’
Notice that [?] is used as a placeholder for the unspecified database name.

As another example, you can execute sp_helpfile on each database by running the following command.
EXECUTE master.sys.sp_MSforeachdb ‘USE [?]; EXEC sp_helpfile’*/

EXEC @RETURN_VALUE = sp_MSforeachdb @command1
— = @command1
–SELECT * FROM #temp

CREATE TABLE #temp2
(
TableID INT IDENTITY
,ServerName nvarchar(100)
,Table_Catalog nvarchar(256)
,Table_Schema nvarchar(256)
,Table_Name SYSNAME
,Column_Name SYSNAME
,Row_Count int
,Table_Size_MB decimal(10,2)
,Table_Size_GB decimal(10,2)
,TableFullName nvarchar(200)
,SQLStatement nvarchar(1000)
,InsertColumn_Statement nvarchar(4000)
,UpdateColumn_Statement nvarchar(4000)
,IsNulled_Statement nvarchar(4000)
,IsNulled_Result nvarchar(100)
)

INSERT INTO #temp2 (ServerName, Table_Catalog, Table_Schema, Table_Name, Column_Name, TableFullName, [Row_Count], Table_Size_MB, Table_Size_GB, SQLStatement, InsertColumn_Statement, UpdateColumn_Statement, IsNulled_Statement, IsNulled_Result)
SELECT
ServerName
,Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,'[‘ + Table_Catalog + ‘].’ + ‘[‘ + Table_Schema + ‘].’ + ‘[‘ + Table_Name + ‘]’
,[Row_Count]
,[Table_Size_MB]
,[Table_Size_GB]
,’SELECT TOP 10 [‘+Column_Name + ‘] From [‘ + Table_Catalog+’].[‘+Table_Schema+’].[‘+Table_Name+’] WHERE [‘+Column_Name+’] IS NOT NULL;’

,’BEGIN TRY ALTER TABLE [‘ + Table_Catalog + ‘].’ + ‘[‘ + Table_Schema + ‘].’ + ‘[‘ + Table_Name + ‘]’ + ‘ ADD SK_CustomerID int; RAISERROR(”SK_CustomerID Column ADDED”, 0, 1); END TRY BEGIN CATCH RAISERROR(”SK_CustomerID Column already exist”, 0, 1); END CATCH;’ AS SK_PaitentID_Statement

/*,’UPDATE target
SET target. [‘ + Column_Name + ‘]=[RefDBTable].[SK_CustomerID]
FROM [‘ + Table_Catalog + ‘].[‘ + Table_Schema + ‘].[‘ + Table_Name + ‘] AS target
INNER JOIN [SUS_SEM].[dbo].[RefDBTable] AS RefDBTable
ON target.[‘ + Column_Name + ‘]=RefDBTable.Number;’ AS UpdateColumn_Statement
*/
/*,’UPDATE target
SET target.[‘ + Column_Name + ‘]=ISNULL([Customer].[SK_CustomerID], 1)
FROM [‘ + Table_Catalog + ‘].[‘ + Table_Schema + ‘].[‘ + Table_Name + ‘] AS target
LEFT JOIN [RefDB].[dbo].[Customer] AS Customer
ON target.[‘ + Column_Name + ‘]=Customer.SK_CustomerID;’ AS UpdateColumn_Statement
*/
,’UPDATE target
SET target.[‘ + Column_Name + ‘]=[Customer].Number
FROM [‘ + Table_Catalog + ‘].[‘ + Table_Schema + ‘].[‘ + Table_Name + ‘] AS target
INNER JOIN [RefDB].[dbo].[Customer] AS Customer
ON target.[‘ + Column_Name + ‘]=convert(varchar(100), Customer.SK_CustomerID);’ AS UpdateColumn_Statement

,’SELECT CASE WHEN COUNT(*)=0 THEN ”NULLED (Number)” ELSE ”EXIST (Number)” END AS IsNulled_Statement
FROM (SELECT TOP 10 [‘+Column_Name + ‘] From [‘ + Table_Catalog+’].[‘+Table_Schema+’].[‘+Table_Name+’] WHERE [‘+Column_Name+’] IS NOT NULL) AS C’
,” AS IsNulled_Result
FROM #temp as t

IF OBJECT_ID(‘[temp].[Statement]’, ‘U’) IS NOT NULL DROP TABLE [temp].[Statement];

SELECT distinct ServerName, Table_Catalog, Table_Schema, Table_Name, Column_Name
,TableFullName
,[Row_Count]
,[Table_Size_MB]
,[Table_Size_GB]
–,SQLStatement
–,InsertColumn_Statement
,UpdateColumn_Statement
–,IsNulled_Statement
–,IsNulled_Result
INTO [temp].[Statement]
FROM #temp2 order by 2, 3, 4
;

IF OBJECT_ID(‘[temp].previewStatement’, ‘U’) IS NOT NULL DROP TABLE [temp].previewStatement;
SELECT *
INTO [temp].previewStatement
FROM [temp].[Statement]
WHERE 1=2
;
SELECT * FROM [temp].[Statement]
;

 

Steps 2 – ETL Toool:

ETL tool will have 3 elements:

  1. Execute SQL: Execute the sql query written in steps 1
  2. Data flow task – select * from result table and flow to another table. Enable data viewer. The purpose of this step is just to see what table and databases will be affected.
  3. Script task:
    1. Read the update query from the table
    2. Execute the update query
    3. Log the execution time, status, table name, table row count, table size

public void Main()
{
/*Name of the log file name*/
String fileName = “H:\\Log\\Log_NHSNumber.txt”;
String rowCount = “”, tableSizeInMB = “”, tableFullName = “”, status = “”;
DateTime today;
ConnectionManager cm;
SqlConnection sqlConn;
//SqlCommand sqlComm, sqlCommCreateStatement, sqlCommExecuteStatement;

/*connect to the DataSources*/
cm = Dts.Connections[“ADONetSUS_SEM”];
sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction);
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
sqlConn.Open();
}

/*get the statement from the tempStatement table*/
var da = new SqlDataAdapter();
var ds = new DataSet();
var dtStatement= new DataTable();
String serverName = “”;
/*get select statement based on the server name*/
serverName = sqlConn.DataSource.ToString();
if (serverName == “PSFADWHPIT01\\nelcsu”)
{
da.SelectCommand = new SqlCommand(“SELECT [Row_Count], [Table_Size_MB], [TableFullName], [UpdateColumn_Statement] FROM [Unify2].[temp].[Statement]”, sqlConn);
}
else
da.SelectCommand = new SqlCommand(“SELECT [Row_Count], [Table_Size_MB], [TableFullName], [UpdateColumn_Statement] FROM [Unify2.1].[temp].[Statement]”, sqlConn);
da.Fill(ds, “dsTableStatement”);
dtStatement = ds.Tables[“dsTableStatement”];

String statement = “”;
foreach(DataRow dr in dtStatement.Rows)
{
rowCount = dr[0].ToString();
tableSizeInMB = dr[1].ToString();
tableFullName = dr[2].ToString();
statement = dr[3].ToString();
var sqlComm = new SqlCommand(statement, sqlConn);
sqlComm.CommandTimeout = 60000;
String line = “”;
try
{
today = DateTime.Now;
{
status = “Started”;
line = string.Format(“Status: {0}, Time: {1}, Table: {2}, RowCount: {3}, TableSizeInMB: {4}”, status, today, tableFullName, rowCount, tableSizeInMB);
/*append the time, table name, status to the log file*/
if (File.Exists(fileName))
{
using (StreamWriter writer = File.AppendText(fileName))
{
writer.WriteLine(line);
}
}
}
/*Execute the query here*/
sqlComm.ExecuteNonQuery();
}
catch (Exception e)
{
/*log the time, error message*/
today = DateTime.Now;
{
status = “Failed”;
line = string.Format(“Status: {0}, Time: {1}, Table: {2}, RowCount: {3}, TableSizeInMB: {4}, Message:{5}”, status, today, tableFullName, rowCount, tableSizeInMB, e.Message);
}
throw e;
}
finally
{
sqlComm.Dispose();
today = DateTime.Now;
/*log the time, error message*/
if (status != “Failed”)
{
status = “Completed”;
line = string.Format(“Status: {0}, Time: {1}, Table: {2}, RowCount: {3}, TableSizeInMB: {4}”, status, today, tableFullName, rowCount, tableSizeInMB);
}
/*append the time, table name, status to the log file*/
if (File.Exists(fileName))
{
using (StreamWriter writer = File.AppendText(fileName))
{
writer.WriteLine(line);
}
}
}
}

da.Dispose();
ds.Dispose();
}

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s