Find the row with the most data
Hi All,
I created this script in a hurry so if you have alterations that will make it more efficient they are welcome. The problem was I had a developer ask me how do i find a row with the most data as an update statement was stopping because it had exceeded the 8060 size. So I quickly put this together, hacking some portions from another script I wrote to output data into insert statements.
Copy the script into a query analyser window. Use the 'select name from sysobjects ....' (in the configuration section) to get the table name. Then run the 'sp_help' on the table to work out a id field that you can then goto to fix the data.
Run the script and it will proceed an output of commands you can copy to another window to run. You should see an output like the following
This hasn't been fully tested with all data types but it was promising as it fixed the issue, i've run this under SQL 2000.
Cheers,
Luis Chiriff
/*
- Find the row with the most data v1.1
- Written by Luis Chiriff
- luis.chiriff@gmail.com @ 11/05/2008
- v1.1 has fix for column matrix and handles uniqueidentifiers differently
*/
-- Variable Declaration
Declare @TableName varchar(150), @FieldIdentifier varchar(150) , @SQL1 nvarchar(4000), @SQL2 nvarchar(4000), @SQL3 nvarchar(4000), @SQL4 nvarchar(4000), @MnID int, @MxID int, @CurrCount int, @Pos int, @CurrField varchar(150)
Declare @FName varchar(150), @FType varchar(75), @FNull char(1), @FIdent int, @LastFName varchar(150), @CurrFName varchar(150)
Declare @FieldNames table (id int identity(1,1) not null, FName varchar(150) null, FType varchar(75) null, FNull char(1) null, FIdent int not null)
SET NOCOUNT ON
SELECT @CurrCount = 0, @Pos = 1, @SQL1 = '', @SQL2 = '', @SQL3 = '', @SQL4 = ''
-- Configuration
--select name from sysobjects where xtype = 'U' order by name asc
--sp_help ITEMLIST_20080423153854
SET @FieldIdentifier = 'ID'
SET @TableName = 'TESTIT'
-- Field Population
insert into @FieldNames (FName,FType,FNull,FIdent)
select C.Name as [ColumnName], (select top 1 T.Name from systypes T where T.xtype = C.xtype) as [Type], (case when C.IsNullable = 1 then '1' when C.IsNullable = 0 then '0' end) as [Nullable],
(case when C.autoval is null then 0 else 1 end) as Ident from syscolumns C where id = object_id(@TableName) order by colid asc
select @MnID = min(id), @MxID = max(id) from @FieldNames
while(@MnID <= @MxID)
Begin
select @FName = FName, @FType = FType, @FNull = FNull, @FIdent = FIdent from @FieldNames where ID = @MnID
if (@FType = 'varchar' or @FType = 'nvarchar' or @FType = 'char' or @FType = 'nchar')
SET @FName = 'isnull(['+@FName+'],'''')'
if (@FType = 'text' or @FType = 'ntext')
SET @FName = 'rtrim(cast(isnull(['+@FName+'],'''') as varchar(8000)))'
if (@FType = 'uniqueidentifier')
SET @FName = 'cast(['+@FName+'] as varchar(256))'
if (@FType = 'sysname')
SET @FName = 'rtrim(cast(isnull(['+@FName+'],'''') as nvarchar(256)))'
if (@FType = 'tinyint' or @FType = 'smallint' or @FType = 'int' or @FType = 'float' or @FType = 'money' or @FType = 'decimal' or @FType = 'numeric' or @FType = 'smallmoney' or @FType = 'bigint' or @FType = 'real')
SET @FName = 'isnull(['+@FName+'],0)'
if (@FType = 'datetime' or @FType = 'smalldatetime')
SET @FName = '(Case when ['+@FName+'] is NULL then '''' else Convert(varchar(19),[' + @FName + '],20) end)'
if (@FType = 'bit')
SET @FName = 'cast((Case when ['+@FName+'] = 1 then 1 else 0 end) as char(1))'
if (@FType = 'binary' or @FType = 'sql_variant' or @FType = 'varbinary')
SET @FName = 'isnull(['+@FName+'],0)'
SET @CurrField = 'DATALENGTH('+@FName+')+'
if (@Pos = 1)
Begin
if ((len(@SQL1)+len(@CurrField)) < 3950)
Begin
SET @SQL1 = @SQL1 + @CurrField
End
else
Begin
SET @SQL2 = @CurrField
SET @Pos = @Pos + 1
End
End
if (@Pos = 2)
Begin
if ((len(@SQL2)+len(@CurrField)) < 3950)
Begin
SET @SQL2 = @SQL2 + @CurrField
End
else
Begin
SET @SQL3 = @CurrField
SET @Pos = @Pos + 1
End
End
if (@Pos = 3)
Begin
if ((len(@SQL3)+len(@CurrField)) < 3950)
Begin
SET @SQL3 = @SQL3 + @CurrField
End
else
Begin
SET @SQL4 = @CurrField
SET @Pos = @Pos + 1
End
End
if (@Pos = 4)
Begin
if ((len(@SQL4)+len(@CurrField)) < 3950)
Begin
SET @SQL4 = @SQL4 + @CurrField
End
else
Begin
PRINT '=== OUT OF SQL VARS ==='
End
End
SET @MnID = @MnID + 1
End
-- Remove extra + at the end
if (@Pos = 1)
SELECT @SQL1 = substring(@SQL1,1,len(@SQL1)-1)
if (@Pos = 2)
SELECT @SQL2 = substring(@SQL2,1,len(@SQL2)-1)
if (@Pos = 3)
SELECT @SQL3 = substring(@SQL3,1,len(@SQL3)-1)
if (@Pos = 4)
SELECT @SQL4 = substring(@SQL4,1,len(@SQL4)-1)
-- Output Command to run in another window
PRINT 'select top 10 '+@FieldIdentifier+', ('
PRINT @SQL1
PRINT @SQL2
PRINT @SQL3
PRINT @SQL4
PRINT ') as ActDataLength FROM '+@TableName+' ORDER BY ('
PRINT @SQL1
PRINT @SQL2
PRINT @SQL3
PRINT @SQL4
PRINT ') desc' 
