Distinct and NULL Counts

 /*

 

SCRIPT: Distinct and NULL Counts

 

*/

 

 

 

SET NOCOUNT ON;

 

DECLARE @SCHEMA_NAME VARCHAR(128) = 'dbo';

DECLARE @TABLE_NAME VARCHAR(128) = 'CAQH_DataSummary_ProviderExtract';

 

-- set query results to text

 

SELECT

  ';WITH CTE AS (' + CHAR(13) + CHAR(10) +

  'SELECT' + CHAR(13) + CHAR(10) ;

 

 

SELECT

  'COUNT(DISTINCT [' +

   COLUMN_NAME + ']) AS [DISTINCT_' + COLUMN_NAME + '], ' +

  'SUM(CASE WHEN [' + COLUMN_NAME + ']  IS NULL THEN 1 ELSE 0 END) AS [NULL_' + COLUMN_NAME + '],' +

  CHAR(13) + CHAR(10)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = @SCHEMA_NAME

AND TABLE_NAME = @TABLE_NAME

AND DATA_TYPE <> 'xml';

 

SELECT

  'COUNT(*) [ROW_COUNT]' + CHAR(13) + CHAR(10) +

  'FROM ' + @SCHEMA_NAME + '.' + @TABLE_NAME  + CHAR(13) + CHAR(10) +

  ')' + CHAR(13) + CHAR(10) ;

 

SELECT

  'SELECT ' +

  '''' + TABLE_NAME  + '''' + ' AS TABLE_NAME,' +

  '''' + COLUMN_NAME + '''' + ' AS COLUMN_NAME,' +

  '[DISTINCT_' + COLUMN_NAME + '] AS [DISTINCT_COUNT], ' +

  '[NULL_' + COLUMN_NAME + '] AS [NULL_COUNT], ' +

  '''' + CASE DATA_TYPE

 WHEN 'varchar' THEN CONCAT('VARCHAR(',  iif(CHARACTER_MAXIMUM_LENGTH = -1, 'max', convert(varchar, CHARACTER_MAXIMUM_LENGTH)), ')')

 WHEN 'nvarchar' THEN CONCAT('NVARCHAR(',  iif(CHARACTER_MAXIMUM_LENGTH = -1, 'max', convert(varchar, CHARACTER_MAXIMUM_LENGTH)), ')')

 WHEN 'char' THEN CONCAT('CHAR(',  CHARACTER_MAXIMUM_LENGTH, ')')

 WHEN 'nchar' THEN CONCAT('NCHAR(',  CHARACTER_MAXIMUM_LENGTH, ')')

 --WHEN 'varchar' THEN 'VARCHAR(' + CONVERT(VARCHAR(30), CHARACTER_MAXIMUM_LENGTH) + ')'

 --WHEN 'nvarchar' THEN 'NVARCHAR('  + CONVERT(NVARCHAR(30), CHARACTER_MAXIMUM_LENGTH) + ')'

 --WHEN 'char' THEN 'CHAR(' + CONVERT(VARCHAR(30), CHARACTER_MAXIMUM_LENGTH) + ')'

 --WHEN 'nchar' THEN 'NCHAR('  + CONVERT(NVARCHAR(30), CHARACTER_MAXIMUM_LENGTH) + ')'

 ELSE UPPER(DATA_TYPE) END + '''' + ' AS COLUMN_TYPE, ' +

  '''' +  '''' + ' AS COMMENTS ' +

  ' FROM CTE UNION ALL' +

  CHAR(13) + CHAR(10)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = @SCHEMA_NAME

AND TABLE_NAME = @TABLE_NAME

AND DATA_TYPE <> 'xml';

 

SELECT

  'SELECT ' +

  '''' + 'EOF' + '''' + ',' +

  '''' + 'EOF' + '''' + ',' +

  '0, 0,' + '''' + 'EOF' + '''' + + ',' +

   '''' + 'EOF' + '''' + CHAR(13) + CHAR(10)



Comments

Post a Comment

Popular posts from this blog

Using sp_executesql with OPENQUERY

 gemini prompt: give me the in depth details on using openquery in sql server with the sp_executesql stored procedure The OPENQUERY function in SQL Server allows you to execute a pass-through query on a specified linked server . By design, OPENQUERY 's arguments—the linked server name and the query string— do not accept local T-SQL variables . However, you can overcome this limitation, particularly for parameterizing the query string, by using dynamic SQL executed via the system stored procedure sp_executesql . Here is an in-depth breakdown of how to use OPENQUERY with sp_executesql to include variable values in your remote query. 1. The Challenge: Parameterizing OPENQUERY The fundamental restriction of OPENQUERY is its syntax: SQL OPENQUERY ( linked_server , 'query' ) Neither linked_server nor the 'query' string can be a local SQL Server variable (e.g., @MyLinkedServerName or @MyQuery ). To pass a variable (like a specific CustomerID or a date range) into...
Read more

Executing Remote Queries Safely and Efficiently with sp_executesql

 gemini prompt: provide a blog post by a database professional on the best practice for using sp_executesql to query a remote sql server database Executing Remote Queries Safely and Efficiently with sp_executesql As database professionals, we often face the need to query data from a remote SQL Server instance . While options like simple four-part names (e.g., [Server].[Database].[Schema].[Table] ) or OPENQUERY exist, using sp_executesql in conjunction with linked servers provides a powerful, secure, and flexible approach, especially when dealing with dynamic SQL. Here's a breakdown of the best practices for leveraging sp_executesql for remote queries. 1. Why sp_executesql for Remote Queries? When executed locally, sp_executesql is primarily used to execute dynamic SQL safely by allowing you to parameterize the query string, which is the fundamental defense against SQL injection . When querying a linked server, you can use it to execute code directly on the remote server...
Read more