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
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).
Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>
|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>> is this possible in T-SQL? (SQL Server 2000)
>> I want to find if the field is not null but empty (has no text in it).
>>
>>
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>sql

Find Fileds Type

How Can I Find Fileds type (for example Bit or nvarchar or ntext ,...) in Sql Server;
with how sql parametr or query ?You could start by look at syscolumns|||Here is what I do in the EntityBroker O/R mapper (in the part about syncrhonizing database schemata) fto find out how the db does look at the moment.

Field types:

::SELECT *
::FROM information_schema.columns
::INNER JOIN information_schema.tables
::ON information_schema.columns.table_catalog = information_schema.tables.table_catalog
::AND information_schema.columns.table_Name = information_schema.tables.table_name
::ORDER BY information_schema.columns.table_catalog,
::information_schema.columns.table_schema,
::information_schema.columns.table_name, information_schema.columns.ordinal_position