Help using Dynamic SQL to query all columns in a table

• shroomshade

  SSC Rookie

  Points: 32

  More actions

  September 21, 2018 at 10:55 am

  #363721

  Hi all,

  I am attempting to create a stored procedure that has the following requirements:

  1. The ability to pass it a table name

  2. Run the below on each column in the table:
  2. SELECT CASE WHEN COUNT(DISTINCT ColumName)
  
  3. The end result should be a single row, with each column populated with  'NULL', 'Single Value' (plus the value)', or 'Multiple Values' based on the results

  This sounds easy enough, but I do not have much experience with Dynamic SQL, and I am having a hard time wrapping my head around the logic. 

  Essentially, I want to take the below code block, which applies this principle to a single table and column, and create a stored procedure that takes a table as a parameter and performs the same operation on all columns in the table:

  SELECT
CASE
    WHEN COUNT(DISTINCT [Column]) IS NULL
        THEN 'NULL'
    WHEN COUNT(DISTINCT [Column]) = '1'
        THEN 'Single Value: ' + (SELECT DISTINCT [Column] FROM [Table])
     WHEN COUNT(DISTINCT [Column]) > '1'
        THEN 'Multiple Values'
END AS [Column]
FROM [Table]
  
  I started the skeleton of a stored procedure, but I am missing something and can't fill in the blanks. Or it may just be completely wrong. Here is what I have so far:

  CREATE PROCEDURE dbo.Proc @table
AS

  BEGIN
    DECLARE @sqlCommand varchar(max) = 'SELECT COUNT(DISTINCT ' + COLUMN_NAME +
    ' FROM ' + @table + ' WHERE '
  
  Could anyone provide some insight on this, or let me know if there is a better way? Any assistance would be appreciated.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 21, 2018 at 12:16 pm

  #2006622

  You mean something like this?
  
DECLARE @SQLCommandTemplate NVARCHAR(MAX)
   ,@SQLCommand NVARCHAR(MAX)
   ,@inTable sysname
   ,@ObjectId INT
   ,@Table sysname
   ,@Schema sysname
   ,@Column sysname;

  SET @SQLCommandTemplate = N'
SELECT
CASE
  WHEN COUNT(DISTINCT [Column]) IS NULL
   THEN ''NULL''
  WHEN COUNT(DISTINCT [Column]) = 1
   THEN ''Single Value: '' + (SELECT DISTINCT CAST([Column] AS VARCHAR(30)) FROM [Table])
  ELSE ''Multiple Values''
END AS [ColumnInfo]
FROM [Table]
';

  SET @inTable = 'HTally';

  -- Validate that the table actually exists
-- NOTE: This will fail if the name exists in multiple schemas

  SELECT
@Schema = OBJECT_SCHEMA_NAME([tab].[object_id])
, @ObjectId = [tab].[object_id]
, @Table = [tab].[name]
FROM
[sys].[tables] AS [tab]
WHERE
[tab].[name] = @inTable; -- Validate that the table actually exists

  DECLARE ColumnCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
[col].[name]
FROM
[sys].[columns] AS [col]
WHERE
[col].[object_id] = @ObjectId
ORDER BY
[col].[column_id];

  OPEN [ColumnCur];

  WHILE 1 = 1
BEGIN
FETCH NEXT FROM [ColumnCur]
INTO @Column;

  IF @@FETCH_STATUS <> 0
  BREAK;

  SET @SQLCommand = REPLACE(REPLACE(@SQLCommandTemplate,'[Table]', QUOTENAME(@Schema) + '.' + QUOTENAME(@Table)),'[Column]', QUOTENAME(@Column));
PRINT @SQLCommand;
EXEC [sys].[sp_executesql] @stmt = @SQLCommand;
END

  CLOSE [ColumnCur];

  DEALLOCATE [ColumnCur];


  This will need some changes to put it in a stored procedure.

• Mike01

  SSChampion

  Points: 11413

  More actions

  September 21, 2018 at 12:26 pm

  #2006626

  I'm not sure why you would need these, but you can try this.  Also, I out in a case for null, but not sure that will ever be used

  declare @table varchar(40) = 'MyTable',
        @schema varchar(20) = 'MySchema'

  DECLARE @sqlCommand varchar(max) = '',
         @sqlcount varchar(max) = ''

  select @sqlCommand = @sqlCommand + 'COUNT(DISTINCT ' + COLUMN_NAME + ') ' + Column_name + ','
        , @sqlcount = @SQLCount + ' case ' + Column_name + ' when null then ''null'' when 1 then ''Single Value: '' + cast(' + Column_name + ' as varchar(10)) else ''Multiple Value: '' + cast(' + Column_name + ' as varchar(10)) end as ' + Column_Name + ','
  from INFORMATION_SCHEMA.Columns
where table_name = @Table
and table_schema = @schema
 
select @sqlCommand = Left(@sqlCommand, len(@SQLCommand) - 1),
        @SQLCount = Left(@SQLCount, len(@SQLCount) - 1)

  --        select 'Select ' + @SQLCount + ' from (Select ' + @SQLCommand + ' from ' + @Schema + '.' + @Table + ') v'
        exec ('Select ' + @SQLCount + ' from (Select ' + @SQLCommand + ' from ' + @Schema + '.' + @Table + ') v')


  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/