Dynamic SQL

• Revenant

  SSC-Forever

  Points: 42467

  More actions

  July 7, 2017 at 10:13 am

  #1949979

  Carlo Romagnano - Friday, July 7, 2017 1:32 AM

  Here a cursor free version:
  DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @sql NVARCHAR(MAX) = '', @max-2 INT
  ,@UnionALL NVARCHAR(11)= '';

  SELECT @sql += @UnionAll + N'SELECT ''' + COLUMN_NAME + ''',MAX(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + ')) FROM ' + QUOTENAME(@TableName)
    ,@UnionALL = ' UNION ALL '
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND TABLE_SCHEMA = @Schema
  AND DATA_TYPE LIKE '%varchar';

    EXEC sp_executesql @sql


  Brilliant!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  July 10, 2017 at 7:55 am

  #1950173

  Carlo Romagnano - Friday, July 7, 2017 1:32 AM

  Here a cursor free version:
  DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @sql NVARCHAR(MAX) = '', @max-2 INT
  ,@UnionALL NVARCHAR(11)= '';

  SELECT @sql += @UnionAll + N'SELECT ''' + COLUMN_NAME + ''',MAX(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + ')) FROM ' + QUOTENAME(@TableName)
    ,@UnionALL = ' UNION ALL '
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND TABLE_SCHEMA = @Schema
  AND DATA_TYPE LIKE '%varchar';

    EXEC sp_executesql @sql


  This is pretty close to what I was thinking too. I would not have written a cursor for this as the question suggested. 😀

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  July 11, 2017 at 1:37 pm

  #1950425

  I would use a no-cursor, no-temp-table version that includes schema.  The value used for @QualifiedTableName can include the schema and any needed brackets.
  DECLARE @QualifiedTableName SYSNAME = 'schema.table';
DECLARE @sql VARCHAR(MAX);

  SET @sql =
'SELECT    ColumnName, MaxLen
FROM (
    ' + STUFF(    ((SELECT REPLACE(REPLACE(' UNION ALL SELECT ColumnName = ''<n>'', MaxLen = MAX(DATALENGTH([<n>])) FROM <t>',
                        '<n>', name), '<t>', @QualifiedTableName)
                FROM sys.columns
                WHERE object_id = OBJECT_ID(@QualifiedTableName) AND TYPE_NAME(system_type_id) LIKE '%varchar'
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')), 1, 10, '')
    + ' ) x';

  EXEC sys.sp_executesql @sql;


• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  July 11, 2017 at 2:30 pm

  #1950435

  Carlo Romagnano - Friday, July 7, 2017 1:32 AM

  Here a cursor free version:
  DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @sql NVARCHAR(MAX) = '', @max-2 INT
  ,@UnionALL NVARCHAR(11)= '';

  SELECT @sql += @UnionAll + N'SELECT ''' + COLUMN_NAME + ''',MAX(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + ')) FROM ' + QUOTENAME(@TableName)
    ,@UnionALL = ' UNION ALL '
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND TABLE_SCHEMA = @Schema
  AND DATA_TYPE LIKE '%varchar';

    EXEC sp_executesql @sql


  Oh wow. I didn't realize until the most recent post that you are using INFORMATION_SCHEMA.TABLE_SCHEMA. This is not a good approach for determining the correct schema. It is not an accurate way to determine the correct schema. Even MS states to NOT use this for the schema. https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• x

  SSC-Insane

  Points: 23660

  More actions

  July 11, 2017 at 3:50 pm

  #1950449

  Sean Lange - Tuesday, July 11, 2017 2:30 PM

  Carlo Romagnano - Friday, July 7, 2017 1:32 AM

  Here a cursor free version:
  DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @sql NVARCHAR(MAX) = '', @max-2 INT
  ,@UnionALL NVARCHAR(11)= '';

  SELECT @sql += @UnionAll + N'SELECT ''' + COLUMN_NAME + ''',MAX(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + ')) FROM ' + QUOTENAME(@TableName)
    ,@UnionALL = ' UNION ALL '
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND TABLE_SCHEMA = @Schema
  AND DATA_TYPE LIKE '%varchar';

    EXEC sp_executesql @sql


  Oh wow. I didn't realize until the most recent post that you are using INFORMATION_SCHEMA.TABLE_SCHEMA. This is not a good approach for determining the correct schema. It is not an accurate way to determine the correct schema. Even MS states to NOT use this for the schema. https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql

  Great thread!

  More detailed info here in my included link, the post suggests that schemas might be ok in information_schema in most cases, but really I'm betting that Microsoft probably just puts a low premium in maintaining information_schema because nobody cares about compatibility anymore and I think that's becoming a majority view with relational database folks anyways.

  http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx

  Anyways, chances are that schema is probably going to be reported ok but here's Carlo's code with my attempted update:

  DECLARE @TableName SYSNAME = 'mytable',@Schema SYSNAME = 'dbo', @sql NVARCHAR(MAX) = '', @max-2 INT
,@UnionALL NVARCHAR(11)= '';
WITH INFORMATION_SCHEMA_COLUMNS (TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE) AS
(SELECT t.name TABLE_NAME, s.name TABLE_SCHEMA, c.name COLUMN_NAME, v.name DATA_TYPE
 FROM sys.tables AS t
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 INNER JOIN sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 INNER JOIN sys.types AS v
 ON c.[system_type_id] = v.[system_type_id])
SELECT @sql += @UnionAll + N'SELECT ''' + COLUMN_NAME + ''',MAX(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + ')) FROM ' + QUOTENAME(@TableName)
 ,@UnionALL = ' UNION ALL '
FROM INFORMATION_SCHEMA_COLUMNS
WHERE TABLE_NAME = @TableName
 AND TABLE_SCHEMA = @Schema
AND DATA_TYPE LIKE '%varchar';
EXEC sp_executesql @sql


• Carlo Romagnano

  SSC-Insane

  Points: 22680

  More actions

  July 12, 2017 at 1:01 am

  #1950470

  Sean Lange - Tuesday, July 11, 2017 2:30 PM

  Carlo Romagnano - Friday, July 7, 2017 1:32 AM

  Here a cursor free version:
  DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @sql NVARCHAR(MAX) = '', @max-2 INT
  ,@UnionALL NVARCHAR(11)= '';

  SELECT @sql += @UnionAll + N'SELECT ''' + COLUMN_NAME + ''',MAX(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + ')) FROM ' + QUOTENAME(@TableName)
    ,@UnionALL = ' UNION ALL '
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND TABLE_SCHEMA = @Schema
  AND DATA_TYPE LIKE '%varchar';

    EXEC sp_executesql @sql


  Oh wow. I didn't realize until the most recent post that you are using INFORMATION_SCHEMA.TABLE_SCHEMA. This is not a good approach for determining the correct schema. It is not an accurate way to determine the correct schema. Even MS states to NOT use this for the schema. https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql

  Sorry, I was focused to "kill" the cursor! 🙂