Selecting columns by their data types in SQL-CodePudding

I'd like to write a generic query that will be run on many different tables. It needs to select all columns from any table it runs on, but there is a catch: the select must exclude the columns with data type 'ntext'. Otherwise it's a simple

select * from <tableName>.

Any ideas?

I was able to create a query that lists all columns in a table that are not 'ntext'. Unfortunately I cannot pass this a parameter to another select, as it returns multiple results.

CodePudding user response：

It's not a great idea, but I don't have a better way for you to achieve what you want.

Basically, you were on the right track with creating a query to hit up the system object views. You'd then turn this into a select statement, and pass it in to a cursor to be iterated over.

DECLARE c CURSOR FOR 
SELECT 'SELECT ''' sName '.' tName ''' AS TableName, '  STRING_AGG('[' cName ']',', ')   ' FROM [' sName '].[' tName ']' AS tSQL
  FROM (
        SELECT c.name AS cName, t.name AS tName, s.name AS sName
          FROM sys.tables t
            INNER JOIN sys.columns c
              ON t.object_id = c.object_id
            INNER JOIN sys.systypes st
              ON c.system_type_id = st.xtype
            INNER JOIN sys.schemas s
              ON t.schema_id = s.schema_id
         WHERE st.name NOT IN ('TEXT', 'NTEXT','IMAGE','BINARY')
           AND (st.name NOT IN ('NVARCHAR','VARCHAR') OR c.max_length < 50)
       ) a
 GROUP BY tName, sName
DECLARE @tSql NVARCHAR(MAX)
OPEN c
FETCH NEXT FROM c INTO @tSql
WHILE @@FETCH_STATUS <> -1
BEGIN
 PRINT @tSQL
 EXEC sp_executeSQL @tSQL
 FETCH NEXT FROM c INTO @tSql
END
CLOSE c
DEALLOCATE c

Cursors are bad, you shouldn't use them, except when you don't have much of a choice. Warning: If you have a lot of tables, with a lot of data, this is going to run for a long time. You shouldn't use this in production until you have fully tested it and determined for yourself that it's not going to cause your system a problem, and you accept that you're doing so at your own risk.

CodePudding user response：

Piggybacking off of @SQLpro's answer. I wanted it to be more flexible. This version will allow you to enter the Table Parameter with or without the schema prefix. It will also allow you to enter no parameter and if no table parameter the query will pull ALL Tables. If the table parameter is typed in without the schema the query will filter on All Schemas where the TableName = @TABLE_NAME.

Another caveat is when using PRINT it does have a max output. This can be accounted for and while you are able to do some magic to allow PRINT to spit out an endless amount of data I instead prefer to set my Query Results Output from the default GRID to TEXT should I need or want to copy out each generated SELECT statement for each table.

Example below using PRINT. PRINT has a character limit and so see 2nd example of how to easily get around this.

/* DynamicSelect.sql 
 | Desc: This version uses PRINT to spit out the SELECT statements.
*/

DECLARE @TABLE_NAME NVARCHAR(261) = 'dbo.CT_DogBreed' --'dbo.CT_DogBreed' 'import.CT_DogBreed'
      , @SQL        NVARCHAR(max) = N''
;

SELECT @SQL = CONCAT(@SQL, N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME, char(13))
    --CONCAT(N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME) AS DynamicSQL
    --, c.TABLE_SCHEMA
    --, c.TABLE_NAME
    --, PARSENAME(@TABLE_NAME, 2) AS SchemaPartFilterOnParameter
    --, PARSENAME(@TABLE_NAME, 1) AS TablePartFilterOnParameter

FROM   INFORMATION_SCHEMA.COLUMNS c
WHERE  DATA_TYPE <> 'ntext'

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 2) IS NOT NULL /* If TRUE then filter on Schema */
             THEN 
                CASE WHEN c.TABLE_SCHEMA = PARSENAME(@TABLE_NAME, 2) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Schema and return 1 for TRUE and not apply Schema Filter - Returns All Schemas */
             ELSE 1 END

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 1) IS NOT NULL /* If TRUE then filter on Table */
             THEN 
                CASE WHEN c.TABLE_NAME = PARSENAME(@TABLE_NAME, 1) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Table and return 1 for TRUE and not apply Table Filter - Returns All Tables  */
             ELSE 1 END

GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME

PRINT @SQL

--EXEC (@SQL);

Not using PRINT - Set Query Results to Text. PRINT has a character limit.

/* DynamicSelect.sql 
 | Desc: Using this version you can output the data to the Grid or
 |       set your Query Ouput from the default GRID output to TEXT.
 |       Comment out all fields except the DynamicSQL Column if you output as TEXT.
*/

DECLARE @TABLE_NAME NVARCHAR(261) = 'dbo.CT_DogBreed' --'dbo.CT_DogBreed' 'import.CT_DogBreed'
      , @SQL        NVARCHAR(max) = N''
;

SELECT --@SQL = CONCAT(@SQL, N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME, char(13))
    CONCAT(N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME) AS DynamicSQL
    , c.TABLE_SCHEMA
    , c.TABLE_NAME
    , PARSENAME(@TABLE_NAME, 2) AS SchemaPartFilterOnParameter
    , PARSENAME(@TABLE_NAME, 1) AS TablePartFilterOnParameter

FROM   INFORMATION_SCHEMA.COLUMNS c
WHERE  DATA_TYPE <> 'ntext'

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 2) IS NOT NULL /* If TRUE then filter on Schema */
             THEN 
                CASE WHEN c.TABLE_SCHEMA = PARSENAME(@TABLE_NAME, 2) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Schema and return 1 for TRUE and not apply Schema Filter - Returns All Schemas */
             ELSE 1 END

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 1) IS NOT NULL /* If TRUE then filter on Table */
             THEN 
                CASE WHEN c.TABLE_NAME = PARSENAME(@TABLE_NAME, 1) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Table and return 1 for TRUE and not apply Table Filter - Returns All Tables  */
             ELSE 1 END

GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME

--PRINT @SQL

--EXEC (@SQL);

Screenshot of how to set the Query Results from the Default Grid to Text. Navigate to Query --> Results to --> Results to Text.

Gif showing both PRINT and Query Results to Text.

CodePudding user response：

Here is a shortest code to do so :

DECLARE @TABLE_NAME NVARCHAR(261) = 'dbo.sys_dm_db_missing_index_details'
               ,@SQL NVARCHAR(max) = N'';
SELECT @SQL = N'SELECT '   STRING_AGG('['   CAST(COLUMN_NAME AS NVARCHAR(max))   ']', ', ')   ' FROM '   @TABLE_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  DATA_TYPE <> 'ntext'
  AND   TABLE_SCHEMA = PARSENAME(@TABLE_NAME, 2)
  AND   TABLE_NAME = PARSENAME(@TABLE_NAME, 1)
EXEC (@SQL);

You just have to replace the name of the table in the @TABLE_NAME variable to use it on a specific table.