2

I have the following code:

select
    [schema].[name]     [schema],
    [table].[name]      [table],
    [column].[name]     [column],
    [type].[name]       [type],
    [column].max_length [maxAllowedLength],
    exec('select max(len(' + [column].[name] + ')) from ' + [table].[name]) maxDataLength
from sys.schemas [schema]
    join sys.tables [table] on [schema].schema_id = [table].schema_id
        join sys.columns [column] on [table].object_id = [column].object_id
            join sys.types [type] on type.system_type_id = [column].system_type_id
where 1=1
and   [schema].[name] = 'dbo'
and   [table].[name]  = 'TableToAnalyze'
order by [schema].[name], [table].[name]

But it doesn't work. Can I do what I'm trying to do? I'd like to also add other information, like minimum length and whether there are any non-numbers, non-dates, dates, etc. to it as well, but that all depends on the basic framework.

In case it's not clear, I want one row per column in the table 'TableToAnalyze', and each row should have the schema, table, column, type, maximum allowable size, and (the part that's giving me trouble is) the maximum actual data length, all for each column in the table I'm analyzing.

It was recommended I use a cross apply, so I tried

select
    [schema].[name]     [schema],
    [table].[name]      [table],
    [column].[name]     [column],
    [type].[name]       [type],
    [column].max_length [maxAllowedLength],
    maxDataLength
from sys.schemas [schema]
    join sys.tables [table] on [schema].schema_id = [table].schema_id
        join sys.columns [column] on [table].object_id = [column].object_id
            join sys.types [type] on type.system_type_id = [column].system_type_id
            cross apply (exec('select max(len(' + [column] + ')) from ' + [table].[name])) maxDataLength
where 1=1
and   [schema].[name] = 'dbo'
and   [table].[name]  = 'TableToAnalyze'
order by [schema].[name], [table].[name]

But that doesn't work either.

6
  • have you looked at a cursor? Write a select to get the list of tables with the conditions you want and then pass them into a cursor with your query above as a variable for each table
    – Alen
    Commented Jul 19, 2018 at 18:21
  • I feel like you can cross apply a table valued function to achieve the result you are looking for here. Commented Jul 19, 2018 at 18:27
  • The dynamic part would be it's own batch, separate from the rest of the batch, so naturally it wouldn't work. I like @Shaulinator suggestion
    – S3S
    Commented Jul 19, 2018 at 19:08
  • @Shaulinator I tried a cross apply, but I don't have much experience with them; my question has what I tried Commented Jul 19, 2018 at 20:55
  • When I meant cross apply a table valued function, that means the exec select is contained in the TVP. By just adding that segment into the cross apply it won't work. I am unable to until after Monday spend time creating a code that could help as a solution. I have two examples that may help you though, or at least will show you what I mean by TVP. Example 1 Commented Jul 19, 2018 at 21:02

2 Answers 2

2

You can never use dynamic SQL in a SELECT or in a function. The Query Optimizer for the outer query wouldn't know what your dynamic batch does, and it might try changing the database. In which case there's no way to build a correct query plan.

2

This is a very convoluted way of doing it but it works, best of luck!

USE AdventureWorks2012
GO

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(2000)
      , @table varchar(128)
      , @schema varchar(128)

SELECT @table = 'Address', @schema = 'Person'

DECLARE @meta AS TABLE (
        [schema_name] varchar(128)
      , [table_name] varchar(128)
      , [column_name] varchar(128)
      , [type] varchar(128)
      , [max_length] int
      , [max_data_length] int)

DECLARE @schema_name varchar(128)
      , @table_name varchar(128)
      , @column_name varchar(128)
      , @type varchar(128)
      , @max_length int
      , @max_data_length int

DECLARE C CURSOR
    FOR SELECT S.[name] [schema_name]
             , T.[name] [table_name]
             , C.[name] [column_name]
             , Y.[name] [type]
             , C.[max_length]
          FROM sys.tables T
         INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
         INNER JOIN sys.columns C on T.object_id = C.object_id
         INNER JOIN sys.types Y ON C.user_type_id = Y.user_type_id
         WHERE T.name = @table
           AND S.[name] = @schema
         ORDER BY C.column_id

    OPEN C
    FETCH NEXT FROM C
     INTO @schema_name, @table_name, @column_name, @type, @max_length

WHILE @@FETCH_STATUS = 0
      BEGIN
      SELECT @SQL = N'SELECT '''+ @schema_name +''' [schema_name], '''+ @table_name +''' [table_name], '''+ @column_name +''' [column_name], '''+ @type +''' [type], '+ CAST(@max_length AS VARCHAR) +' [max_length], '+ CASE WHEN @type IN ('char','nchar','varchar','nvarchar') THEN ' MAX(LEN(['+ @column_name +']))' ELSE ' MAX(LEN(NULL))' END +' [max_data_length] FROM ['+ @schema_name +'].['+ @table_name +']'

      INSERT INTO @meta EXEC (@SQL)
      FETCH NEXT FROM C
      INTO @schema_name, @table_name, @column_name, @type, @max_length
END
CLOSE C
DEALLOCATE C

SELECT * FROM @meta
1
  • I wound up just building a dynamic query out of unions, where each query would do analysis on one column. Was hoping for something more elegant, but it works. I'll look into cursors though, thanks! Commented Jul 24, 2018 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.