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
BSNL Recharge Plans
ReplyDeleteYou may check the specific daily prepaid recharge offers, one year plan of Bharat Sanchar Nigam Limited introduced in circles like Tamilnadu, Kerala, Karnataka, Gujarat, Maharashtra etc BSNL Recharge Plans and also find BSNL all India free roaming facility including Mumbai and Delhi by allowing new prepaid mobile plan with unlimited data and calls, free SMS, even in roaming, and to attract more customers into their web.
Hello, guys, I wanted to thank you for sharing this useful material. Frankly speaking, I'm not quite good at programming and coding nevertheless. My brother will be interested in this SQL server databases this is his personal page https://shop.aer.io/seanscot
ReplyDeleteWhen considering different resume writing services for salon receptionist job description for resume search, I chose ResumeGets and couldn't be more pleased. Their team prepared a high-level resume for me, taking into account all my unique skills and experience in the reception area. This helped me stand out from the other candidates and land the receptionist position I wanted.
ReplyDeleteI enjoyed this articlesekho.pk! Your writing style is both clear and engaging.
ReplyDeleteHow to get Maximum and Minimum Length for each Column value for all tables in your SQL Server Database is useful, I think this is a right way, I will share it for poppy playtime chapter 3 and I find it is totally worth it
ReplyDelete