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.
