BLandry474 (10/20/2016)

---

Here is my brilliant idea (that I am sure someone else already thought of...)

We have a situation where we regularly get tables from another department and we have to check every text-related column to see if the users actually "over-typed" something. What I mean is that they were looking away from the keyboard and typed more than 255 chars. without realizing it - saved the record - and never knew they had done this.

So the boss asked if I could write a SProc to detect this kind of thing. E.g. look at the columns, and see if they were "full" (for lack of a better term). So the first thing I did was to run:

EXEC sp_columns @table_name = 'WHATEVER_THE_TABLE_IS' and this gives me a nice display of ALL the internal makeup of the table - BUT!!! - I don't need all of it. I just need a few columns like LENGTH and PRECISION.

So here is the simple (I hope) question...

Can I develop a query that would allow me to specify the columns out of what I presume is Master? Again, I don't need all of it - just a few.

Thanks in advance for any help you can offer.

BLandry474 (10/21/2016)

---

My thanks to all who responded, but please, in the future, read the question - I DID NOT say "Users are entering data", what I said was "We get tables" - meaning these are tables exported from other systems where the limits are not the same as SQL Server. So a few of you got off on a tangent answering a question I never asked.

To those who presented good, workable solutions, many thanks - it's most helpful. To the others, I honor your excellent knowledge; you're way ahead of me - but deep knowledge is kind of useless if you're answering a question that was in fact, never asked.

No offense intended here to anyone, but as the wise man says; "intelligence is useless without common sense."

BLandry474 (10/21/2016)

---

Alex,

Here is the basic code I used. This produces a nice temp table of information and of course, you can edit the query if you don't want all the columns. I am using this in a larger SProc that will allow my user to add the table he wants to compare as a parameter. Works well so far! Obviously, from this basic code, you could tinker it to meet your own needs.

Create table TempTable

(TABLE_QUALIFIER varchar(40),

TABLE_OWNER varchar(20),

TABLE_NAME varchar(40),

COLUMN_NAME varchar(40),

DATA_TYPE int,

TYPE_NAME varchar(20),

PREC int, LENGTH int,

SCALE int, RADIX int,

NULLABLE char(4),

REMARKS varchar(128),

COLUMN_DEF varchar(40),

SQL_DATA_TYPE int,

SQL_DATETIME_SUB int,

CHAR_OCTET_LENGTH int,

ORDINAL_POSITION int,

IS_NULLABLE char(4),

SS_DATA_TYPE int)

Set nocount on

Insert TempTable

Exec sp_columns @table_name = 'YOUR TABLE NAME'

Select * From TempTable

Drop table TempTable