With the help of below SQL script we will get the max length
of the data of the table.
declare @sql varchar(8000),
@table_name varchar(50)='Employee',
@table_schema varchar(50)='HumanResources';
set @sql = 'select '
select @sql += 'max(len( ' + column_name+ ')) AS ' + column_name + ','
from information_schema.columns
where table_name = @table_name
and TABLE_SCHEMA= @table_schema
and data_type in('varchar','char','nvarchar','nchar')
set @sql = left(@sql,len(@sql) -1)
set @sql +=' from '+ @table_schema+'.'+@table_name
exec (@sql)
|
Basically
this script create a dynamic sql script to get the max length of the column.
Here I am
getting only varchar, nvarchar, char or nchar data type column.
The below script is generating this script.
select
max(len( NationalIDNumber)) AS NationalIDNumber,
max(len( LoginID)) AS LoginID,
max(len( JobTitle)) AS JobTitle,
max(len( MaritalStatus)) AS MaritalStatus,
max(len( Gender)) AS Gender
from HumanResources.Employee
|
We will get the same result as above.