How to get Maximum and Minimum Length for each Column Values for all the tables in SQL Server Database

Scenario:

Let's say you are working as SQL Server developer / Architect and you need to design tables from existing tables. While creating existing tables nobody thought about correct data types and used NVARCHAR(MAX) or VARCHAR(MAX) , text or even ntext. Your goal is analyse the data and then come up best data types for each column. While analysis you need to know the max length for the values your columns so you can proviude max data type length for new table columns.

The below script is going to run on entire database and get the maximum and minimum length from each of the column for each of the table and return you at the end. This query can take quite a long time depending upon the number of tables, columns and records.

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
DECLARE @DataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,MaxLength VARCHAR(50)
    ,MinLength VARCHAR(50)
    )

DECLARE Cur CURSOR
FOR
SELECT DB_Name() AS DatabaseName
    ,s.[name] AS SchemaName
    ,t.[name] AS TableName
    ,c.[name] AS ColumnName
    ,'[' + DB_Name() + ']' + '.[' + s.NAME + '].' + '[' + T.NAME + ']' AS FullQualifiedTableName
    ,d.[name] AS DataType
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE d.NAME LIKE '%char%'
or d.name like '%Text%'


OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' + @SchemaName + ''' AS SchemaName,
      ''' + @TableName + ''' AS TableName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DataType + ''' AS ColumnDataType,
      (Select MAX(LEN(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)))) from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS MaxLength,
      (Select MIN(LEN(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)))) from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS MinLength'

    PRINT @SQL

    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END

CLOSE Cur

DEALLOCATE Cur

SELECT *
FROM #Results

How to get max and min column value length for all the columns in SQL Server Database

How to create SSRS Report Dynamically with Dynamic Schema, Dynamic Table Name and Dynamic Columns Part 59.1

Scenario:

Few months back, I created a post/video "How to Create SSRS Report When Number of Columns Can change Anytime in Source Object". In this post we are going to create another dynamic reports in which we will be able to

1) Choose the Schema form our Database
2) Choose Table for given schema from step 1
3) Choose column/s depending upon values of step 1 and Step 2
4) Display the data on report for our selected columns

This type of report can be very helpful when we have to simply create many details reports for users from our database. If we would like we can add also the Where clause in our report to provide filtered reports. Let's leave that challenge to you.


SQL Queries for our Data Sets

1) DS_Report 
Link to Code


3) DS_Tables
This will return the list of user tables depending upon the schema you will choose

Select Distinct Table_Name as TableName from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
and TABLE_SCHEMA=@SchemaName
order by Table_Name


4) DS_Columns
This will return us the list of columns depending upon the selection of Schema and Table

Select COLUMN_NAME as ColumnName from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA=@SchemaName
and TABLE_NAME=@TableName