Finding Max Length of ntext column in SP

I have upsized an access database and am trying to find all the maximum
lengths of the columns, so I can restructure the DB. I have the query that
will give me the max length of the column, but since I have a bunch of
columns, I would like to write a SP to do it for all the columns.
Here is my query
SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
FROM tblIPPCore2
ORDER BY MembersLenMax DESC;
How can I automate this query?
Thanks,
DrewI have modified my query to the following,
SELECT MAX(DATALENGTH([Members Present])) AS MembersPresentLenMax
FROM tblIPPCore2
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>|||Here is the answer...
This Procedure takes @.TableName, and returns the maximum lengths for each
column in the table. There is no error correction, so column names must not
have spaces or weird characters.
CREATE PROCEDURE spMaxLen
@.TableName varchar(20)
AS
declare @.v_tempTable TABLE (myid bigint identity(1,1), sSQL varchar(1000))
DECLARE @.vMaxCount int, @.iCount int, @.sSQL varchar(1000)
INSERT into @.v_tempTable (sSQL)
SELECT 'SELECT MAX(DATALENGTH([' + column_name + '])) AS ' +
replace(column_name, ' ', '') + 'MaxLen FROM ' + @.TableName
FROM information_schema.columns
where table_name = @.TableName
SET @.vMaxCount =@.@.ROWCOUNT
set @.iCount = 1
WHILE @.iCount < @.vMaxCount + 1
BEGIN
SELECT @.sSQL = sSQL from @.v_tempTable where myid = @.iCount
exec (@.ssql)
SET @.iCount = @.iCount + 1
END
GO
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>