Category Archives: SQL Server

Converting CSV to Table rows in SQL Server and Oracle

Another useful block of code that will help in converting CSV to a table row.

with temp as
(
    select 108 Id, 'australia, australia, united states of america, iran' str
    UNION ALL
    select 109, 'india, iran, iraq'
    UNION ALL
    SELECT 109, 'israel, australia'
), xmlData as
(
     SELECT Id,  
         CAST ('<M>' + REPLACE(str, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  temp
)
SELECT xmlData.Id,  
     ltrim(rtrim(Split.a.value('.', 'VARCHAR(100)'))) AS Data  
FROM  xmlData CROSS APPLY Data.nodes ('/M') AS Split(a)

and in Oracle,

with temp as
(
    select 108 Id, 'australia, australia, united states of america, iran' str  from dual
    UNION ALL
    select 109, 'india, iran, iraq' from dual
    UNION ALL
    SELECT 109, 'israel, australia' FROM dual
)
SELECT DISTINCT ID, ltrim(regexp_substr(str, '[^' || ',' || ']+', 1, LEVEL)) str
FROM temp T
CONNECT BY regexp_substr(str, '[^' || ',' || ']+', 1, LEVEL) IS NOT NULL
ORDER BY ID;

Preparing NULL counts for SQL and Oracle data tables

I am posting this one after a very long time. Just thought this information will be useful for someone.

Here is the T-SQL for SQL Server that will fetch the columns in a table and provide you the NULL, ZERO, NEGATIVE and UNIQUE count of each table columns.

USE <database>
GO

BEGIN
	SET NOCOUNT ON
	DECLARE @TAB NVARCHAR(100) = <table_name>
	DECLARE @SQL NVARCHAR(500)
	DECLARE @RECORDS INT, @TOTRECORDS INT
	DECLARE @OBJECT_ID INT,
		@TOTAL INT
	DECLARE @COL NVARCHAR(100),
		@COLTYPE NVARCHAR(100),
		@TS NVARCHAR(100)
	SELECT @OBJECT_ID = OBJECT_ID FROM SYS.TABLES WHERE NAME = @TAB

	DECLARE col_cursor CURSOR FOR 
	SELECT	COLUMNS.NAME COLNAME, SYSTYPES.NAME COLTYPE
	FROM SYS.COLUMNS 
		INNER JOIN SYS.systypes ON SYSTEM_TYPE_ID = XUSERTYPE
		WHERE object_id = @OBJECT_ID;

	OPEN col_cursor
	FETCH NEXT FROM col_cursor INTO @COL, @COLTYPE

	set @sql = N'SELECT @ptotrecords = count(1) FROM ' + @TAB 
	EXECUTE sp_executeSQL
		@sql,
        N'@ptotrecords INT OUTPUT', @ptotrecords=@totrecords OUTPUT

    WHILE @@FETCH_STATUS = 0
    BEGIN
		set @sql = N'SELECT @precords = count(1) FROM ' + @TAB + ' WHERE ' + @COL + ' IS NULL'
		EXECUTE sp_executeSQL
			@sql,
            N'@precords INT OUTPUT', @precords=@records OUTPUT
		PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'NULL' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))

		set @sql = N'SELECT @precords = count(distinct ' + @COL + ') FROM ' + @TAB  + ' WHERE ' + @COL + ' IS NOT NULL'
		EXECUTE sp_executeSQL
			@sql,
            N'@precords INT OUTPUT', @precords=@records OUTPUT
		PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'UNIQUE' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))

		IF @COLTYPE IN ('MONEY', 'NUMERIC', 'INT') 
		BEGIN
			set @sql = N'SELECT @precords = count(1) FROM ' + @TAB + ' WHERE ' + @COL + ' = 0'
			EXECUTE sp_executeSQL
				@sql,
				N'@precords INT OUTPUT', @precords=@records OUTPUT
			PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'ZERO' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))
			set @sql = N'SELECT @precords = count(1) FROM ' + @TAB + ' WHERE ' + @COL + ' < 0'
			EXECUTE sp_executeSQL
				@sql,
				N'@precords INT OUTPUT', @precords=@records OUTPUT
			PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'NEGATIVE' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))
		END
		FETCH NEXT FROM col_cursor INTO @COL, @COLTYPE
    END
	CLOSE col_cursor;
	DEALLOCATE col_cursor;
END

PL/SQL code below does a similar job in Oracle.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
   L_ROW_COUNT                        PLS_INTEGER;
   L_TOTAL_COUNT                        PLS_INTEGER;
   COL  VARCHAR(200);
BEGIN
  FOR Y IN (SELECT * FROM ALL_TABLES WHERE TABLESPACE_NAME = '<table space name>' AND TABLE_NAME LIKE '<table name>%'  ORDER BY OWNER, TABLE_NAME ) LOOP
    EXECUTE IMMEDIATE    'SELECT COUNT(*)FROM ' || Y.OWNER || '.' || Y.TABLE_NAME INTO L_TOTAL_COUNT;
     FOR x IN (SELECT * FROM all_tab_columns WHERE table_name = Y.TABLE_NAME and owner = Y.OWNER ORDER BY  column_id ) LOOP
        COL := X.TABLE_NAME || '.' || X.COLUMN_NAME || '[' || X.DATA_TYPE || ']' || CHR(9) ;
  
        EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM ' || X.OWNER || '.' || X.TABLE_NAME || ' WHERE ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ' IS NULL' INTO L_ROW_COUNT;
        DBMS_OUTPUT.PUT_LINE (COL || 'NULL' || CHR(9) || TO_CHAR (l_row_count, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
  
        EXECUTE IMMEDIATE    'SELECT COUNT(DISTINCT ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ') FROM ' || X.OWNER || '.' || X.TABLE_NAME INTO L_ROW_COUNT;
        DBMS_OUTPUT.PUT_LINE (COL || 'UNIQUE' || CHR(9) || TO_CHAR (L_ROW_COUNT, 'fm999,999,999,990')|| CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
        IF X.DATA_TYPE IN ( 'NUMBER') THEN
          EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM ' || X.OWNER || '.' || X.TABLE_NAME || ' WHERE ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ' = 0' INTO L_ROW_COUNT;
          DBMS_OUTPUT.PUT_LINE (COL || 'ZERO' || CHR(9) || TO_CHAR (L_ROW_COUNT, 'fm999,999,999,990')|| CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
          
          EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM ' || X.OWNER || '.' || X.TABLE_NAME || ' WHERE ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ' < 0' INTO L_ROW_COUNT;
          DBMS_OUTPUT.PUT_LINE (COL || 'NEGATIVE' || CHR(9) || TO_CHAR (L_ROW_COUNT, 'fm999,999,999,990')|| CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
        END IF;
     END LOOP;
  END LOOP;
END;

User-defined table types

With SQL Server 2008’s user-defined table types, it is easy to pass a collection of rows to a procedure. Moreover, these table types can also be consumed from the programming languages using collection objects. Read here for more about Table valued parameters.

CREATE TYPE dbo.addr AS TABLE
(
	id      NVARCHAR(100),
	city    NVARCHAR(200),
	zip     NVARCHAR(10),
    PRIMARY KEY (id)
)
GO

CREATE PROCEDURE update_addresses
	@emp_addr addr READONLY
AS
BEGIN
    UPDATE EMP
       SET city = a.city
    FROM EMP e INNER JOIN @emp_addr a ON e.emp_id = a.id
END

Script to compare two SQL databases

Here is a simple and a minimal level of SQL scripting for comparing two databases. Download COMPARE_SQL_DATABASES.sql (3.53 kb) and change the script as required. And remember to change the variables that sets the databases to be compared before executing them in SQL Studio. The script starts like this and the complete script is available for download here

———————————————————————–
– REPORT TO GENERATE A COMPARISON LIST BETWEEN TWO SQL DATABASES
– THIS REPORT LIST THE DIFFERING TABLES, VIEWS, COLUMNS, AND ROUTINES
———————————————————————–
–THREE VARIABLES NEEDS TO BE CHANGED BEFORE RUNNING THIS REPORT
–THIS REPORT ASSUMES THAT THE SECOND DATABASE IS ADDED AS A LINKED_SERVER
–@localdb1: FIRST DATABASE
–@linkeddb2: DATABASE TO BE COMPARED AGAINST
–@linkedserver2: LINKED SERVER NAME WHERE @linkeddb2 RESIDES
–
–RUN THIS FROM localdb1
declare @localdb1 varchar(10), @linkeddb2 varchar(10)
declare @linkedserver2 varchar(10)
select @linkedserver2 = 'prod'
select @localdb1 = 'nwind', @linkeddb2 = 'nwind'
print 'Tables in [' + @localdb1 + '] and not in [' + @linkedserver2 + '].[' + @linkeddb2 + ']'
exec ('select TABLE_NAME from [' + @localdb1 + '].information_schema.tables a' +
' where not exists(select * from [' + @linkedserver2 + ']' +
'.[' + @linkeddb2 + '].information_schema.tables b where a.table_name = table_name)'
)
print 'Tables in [' + @linkedserver2 + '].[' + @linkeddb2 + '] and not in [' + @localdb1 + ']'
exec (
'select TABLE_NAME from [' + @linkedserver2 + '].[' + @linkeddb2 + '].information_schema.tables a' +
' where not exists(select * from [' + @localdb1 + ']' +
'.information_schema.tables b where a.table_name = table_name)'
)

Using SQL Server’s thesaurus for an extended search

I was trying to differentiate Business and Individual customers in the Customer table. The easiest I found is to use the SQL thesaurus.

SELECT * FROM [Customer] WHERE CONTAINS (custname,'formsof(thesaurus, "ltd")')

Fulltext indexing should be enabled to perform this. This should return customers with names of all forms of limited.

Creating Managed Stored Procedure in SQL Server using Dotnet Assemblies

Will do it in four steps

Step 1: Create mySample.cs file

using System;
public class mySample
{
 public static int Difference(string a, string b)
 {
  return 100;
 }
} 

Step 2: Compile mySample.cs file to generate a library

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /out:mySample.dll /target:library mySample.cs

Step 3: Create Assembly in SQL server and an external function

EXEC sp_configure 'clr enabled', 1
RECONFIGURE

CREATE ASSEMBLY [myFunctions]
AUTHORIZATION [dbo]
FROM 'E:\Prabhu\dotnet\mySample.dll'
WITH PERMISSION_SET = SAFE
go

CREATE FUNCTION Diff(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS int EXTERNAL NAME myFunctions.mySample.Difference
GO 

Step 4: Simply use it

SELECT dbo.diff('a', 'b')

Getting count of Nulls and NonNulls column totals from all tables in a database

The SQL given here will display all the table columns in the database followed by rows having nulls and non-nulls. It has to be executed in multiple phases.

The first one generates the insert script which can be later executed to collect the information from respective table.

--The following SQL statement create sql queries that will collect
--NULLS/non-NULLS from all tables in the database
;with TableObjects as (
       select name TableName, Id TableId 
       from sysobjects where xtype = 'u'
), TableColumns as
(
       Select TableName, Name TableColumn, ColId 
       From SysColumns c
              inner join TableObjects o on o.tableid = c.id
       WHERE
       --exceptions add 
       not exists (select * from systypes t 
              where t.xtype = c.xtype and name in ('image', 'text'))
)
Select 
'INSERT INTO TableStudy 
Select ''' + TableName + ''' TableName,''' + TableColumn +  ''' ColumnName,
        (Select count(*) From [' + TableName + '] Where [' + TableColumn + '] 
                     is NULL) NULLs,
        (Select count(*) From [' + TableName + '] Where [' + TableColumn + '] 
                     is not NULL) NonNULLs;
GO
' 
From TableColumns


--Execute the above results in another database
--which can take hours or days to run depending on data
--before execution create the following table
--create table TableStudy (
--     TableName VARCHAR(100),
--     ColumnName    VARCHAR(100),
--     Nulls  INT,
--     NonNulls      INT
--)

--drop table TableStudy

Selecting data in SQL Server as Hierarchical XML

I will show it in two lines (one the actual query and then the result) and the rest you can search from msdn.

Select 
	OrderId,
	(select 
		ProductId as "@ProductId", 
		UnitPrice as "@UnitPrice", 
		Quantity as "@Quantity", 
		UnitPrice*Quantity as "@Amount" from Northwind..[Order Details] 
	 Where OrderId = Orders.OrderId For XML Path('Product'), Type) as "Products",
	OrderDate
From Northwind..Orders
For XML Path('Order'), Elements XSINIL,
Root('Orders')

You can see the SQL by yourself here. xml.sql (363.00 bytes)

The SQL demostrates usage of For XML, showing columns as attributes or as elements and one level or hierarchy.

Character-separated List of Row values (with Delimiter as parameter)

A minor correction to fn_ConvertCSVToTable posted in Character-separated List to Row Values is available here for download: fn_ConvertCSVToTable.sql (730.00 bytes), which can be called as below:

select * from dbo.fn_ConvertCSVToTable ('1,2,3,4,5,6,7,8,9,0', ',') Numerals 

to return values as rows. The delimiter is also available as a parameter.

Importing and Querying Typed XML in SQL Server

Here is a clear example that demonstrates how to import XML data to SQL table objects and query them. It includes:

The sample XML schema (sdn.xsd) and XML data (sdn.xml) used in this example are taken from SDN List provided by US Department of Treasury. More about the SDN List is available here.

The complete sample is available for download here sdn.sql (1.51 kb) (change the xsd and xml paths in sdn.sql before running it). This uses the [northwind] database and can be changed if required. Also note that the variables used within (commented) can be changed to nvarchar if necessary.