How to: Find varchar columns in a table

microsoft.public.sqlserver.programming

22 Sep 2005 4:14 PM

JDP@Work

How can I find all the varchar columns in a table?

I'm looking for an existing empty or null column rather than add a column.

There have been quite a few un-used columns that were added years ago for
processes that where either never implemented or are no longer needed.

In the past I've just scanned the records in the application and looked for good
candidates by watching for fields that I wasn't familiar with and appeared
empty.

Assuming that my familiarity or not with the fields implied that I could use
them for my own purposes, I'm the programmer now, (sung to the tune of that old
NYC spritual, "I'm walk'in here, I'm walk'in here....")

I'd then query the table for those columns and view any data in them, plus check
other API processes to see if there was a dependency.

Anyway, this was time consuming... scrolling through the database in the
application.

If I could find the varchar columns, I'd put them along with their recID's into
a temp table and query for counts where the columns are empty or null.

I should be able to quickly see the best candidates for column re-use.

Also, can I detect the size of the column(s)?

TIA

JeffP......

22 Sep 2005 4:23 PM

SQL

select column_name,data_type,coalesce(column_default,'') as
column_default,is_nullable,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX ,
NUMERIC_SCALE
from INFORMATION_SCHEMA.COLUMNS
where table_name = @chvYourTableName

data_type will give you the type (varchar etc)

for size of column(s) take a look at http://sqlservercode.blogspot.com/2005/09/average-length-of-all-rows-in-table.html

there is a proc you can use (instructions included)

Show quote

"JDP@Work" wrote:

> How can I find all the varchar columns in a table?
>
> I'm looking for an existing empty or null column rather than add a column.
>
> There have been quite a few un-used columns that were added years ago for
> processes that where either never implemented or are no longer needed.
>
> In the past I've just scanned the records in the application and looked for good
> candidates by watching for fields that I wasn't familiar with and appeared
> empty.
>
> Assuming that my familiarity or not with the fields implied that I could use
> them for my own purposes, I'm the programmer now, (sung to the tune of that old
> NYC spritual, "I'm walk'in here, I'm walk'in here....")
>
> I'd then query the table for those columns and view any data in them, plus check
> other API processes to see if there was a dependency.
>
> Anyway, this was time consuming... scrolling through the database in the
> application.
>
> If I could find the varchar columns, I'd put them along with their recID's into
> a temp table and query for counts where the columns are empty or null.
>
> I should be able to quickly see the best candidates for column re-use.
>
> Also, can I detect the size of the column(s)?
>
> TIA
>
> JeffP......
>
>
>
>
>

22 Sep 2005 4:29 PM

Tibor Karaszi

Strange requirement..., but here's one option:

CREATE TABLE t(c1 char(5), c2 varchar(10) NULL, c3 varchar(20), c4 varchar(20))

insert into t (c1, c2, c3, c4) VALUES('w', NULL, '', 'sdfs')
insert into t (c1, c2, c3, c4) VALUES('w', NULL, '', 'sfsfdsfd')
insert into t (c1, c2, c3, c4) VALUES('w', 'sdfsd', '', 'sdfs')

SELECT
SUM(LEN(c1)) AS c1
,SUM(LEN(c2)) AS c2
,SUM(LEN(c3)) AS c3
,SUM(LEN(c4)) AS c4
FROM t

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:%234KtJ$4vFHA.3936@TK2MSFTNGP10.phx.gbl...
> How can I find all the varchar columns in a table?
>
> I'm looking for an existing empty or null column rather than add a column.
>
> There have been quite a few un-used columns that were added years ago for
> processes that where either never implemented or are no longer needed.
>
> In the past I've just scanned the records in the application and looked for good
> candidates by watching for fields that I wasn't familiar with and appeared
> empty.
>
> Assuming that my familiarity or not with the fields implied that I could use
> them for my own purposes, I'm the programmer now, (sung to the tune of that old
> NYC spritual, "I'm walk'in here, I'm walk'in here....")
>
> I'd then query the table for those columns and view any data in them, plus check
> other API processes to see if there was a dependency.
>
> Anyway, this was time consuming... scrolling through the database in the
> application.
>
> If I could find the varchar columns, I'd put them along with their recID's into
> a temp table and query for counts where the columns are empty or null.
>
> I should be able to quickly see the best candidates for column re-use.
>
> Also, can I detect the size of the column(s)?
>
> TIA
>
> JeffP......
>
>
>
>

22 Sep 2005 4:37 PM

Jeremy Williams

OK, the short answer is:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'

This will show just about anything you wish to know (including maximum
character length) about every column in your database that has a data type
of VarChar.

As an aside, please say you will at least rename the column (using
sp_rename) if you find a good candidate. I have worked on (or struggled
with) several databases in the past where the column names had nothing to do
with the information being stored in them. This is a real maintenance
nightmare, and is really not necessary most of the time.

Show quote

"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:%234KtJ$4vFHA.3936@TK2MSFTNGP10.phx.gbl...
> How can I find all the varchar columns in a table?
>
> I'm looking for an existing empty or null column rather than add a column.
>
> There have been quite a few un-used columns that were added years ago for
> processes that where either never implemented or are no longer needed.
>
> In the past I've just scanned the records in the application and looked
> for good
> candidates by watching for fields that I wasn't familiar with and appeared
> empty.
>
> Assuming that my familiarity or not with the fields implied that I could
> use
> them for my own purposes, I'm the programmer now, (sung to the tune of
> that old
> NYC spritual, "I'm walk'in here, I'm walk'in here....")
>
> I'd then query the table for those columns and view any data in them, plus
> check
> other API processes to see if there was a dependency.
>
> Anyway, this was time consuming... scrolling through the database in the
> application.
>
> If I could find the varchar columns, I'd put them along with their recID's
> into
> a temp table and query for counts where the columns are empty or null.
>
> I should be able to quickly see the best candidates for column re-use.
>
> Also, can I detect the size of the column(s)?
>
> TIA
>
> JeffP......
>
>
>
>

22 Sep 2005 4:39 PM

Aaron Bertrand [SQL Server MVP]

> How can I find all the varchar columns in a table?
>
> I'm looking for an existing empty or null column rather than add a column.

This will generate a script for the current database that will tell you all
of the varchar or nvarchar columns, and what percentage of them are
"unused" -- either NULL or empty strings.

SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','''
    +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)
    +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
    +' WITH (NOLOCK) WHERE LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''')))=''''
    UNION
    SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','''
    +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)
    +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
    +' WITH (NOLOCK)'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE LIKE '%varchar'

You can run this in Query Analyzer, and it will generate a script in the
bottom pane. Copy to a new query window and let 'er rip. Should take a
while on larger databases. I use WITH (NOLOCK) for this kind of task to
avoid concurrency and blocking issues; if you need an up-to-the-millisecond
guaranteed read on the whole table, you may opt to leave that out (but I do
not recommend it on a production server).

A