January 20, 2015 at 5:19 am
Hello,
I am working on a migration project and need to derive the total length of the columns for a particular table. I have the following query designed for the same:
select
t.name,
sum(c.max_length)
from sys.all_columns c
join sys.all_objects t
on t.object_id = c.object_id
where t.name='test_1'
group by t.name
;
This query gives me correct results except for data types varchar(max), nvarchar and xml for which the max_length is stored as -1 which causes my results to be incorrect when summed up. Is there any other way to find out the maximum data length for such type of columns?
Here is my table DDL:
CREATE TABLE [dbo].[TEST_1](
[COL_1] [bigint] NULL,
[COL_2] [bit] NULL,
[COL_3] [char](10) NULL,
[COL_4] [date] NULL,
[COL_5] [datetime] NULL,
[COL_6] [datetime2](7) NULL,
[COL_7] [decimal](18, 0) NULL,
[COL_8] [float] NULL,
[COL_9] [int] NULL,
[COL_10] [money] NULL,
[COL_11] [nchar](40) NULL,
[COL_12] [ntext] NULL,
[COL_13] [numeric](15, 0) NULL,
[COL_14] [nvarchar](max) NULL,
[COL_15] [nvarchar](300) NULL,
[COL_16] [real] NULL,
[COL_17] [smalldatetime] NULL,
[COL_18] [smallint] NULL,
[COL_19] [text] NULL,
[COL_20] [tinyint] NULL,
[COL_21] [uniqueidentifier] NULL,
[COL_22] [varchar](100) NULL,
[COL_23] [varchar](max) NULL,
[COL_24] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
January 20, 2015 at 5:28 am
Those columns don't have maximum lengths. They can be as long as the data in them. Probably best to sum up the lengths for all the columns that do have maximums, and to keep a separate list of the other columns. You can then look at the actual data in those columns to get a feel for how much data is in them.
John
January 20, 2015 at 7:05 am
Thank You.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply