Sterling Ostin (10/26/2016)

---

Sergiy,

I did read Luis' post and incorporated it in an even more graceful and shorter solution that best suited my need.

In the future, maybe you ought to question what you presume you "know" about other people and their actions.

🙂

I did read Luis' post and incorporated it in an even more graceful and shorter solution that best suited my need.

Luis Cazares (10/20/2016)

---

Perhaps something like this can help you start?

SELECT 'SELECT * FROM ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME

+ ' WHERE ' + STUFF(( SELECT ' OR LEN( ' + c.COLUMN_NAME + ') = ' + CAST( c.CHARACTER_MAXIMUM_LENGTH AS varchar(4))

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.TABLE_NAME = t.TABLE_NAME

AND c.TABLE_SCHEMA = t.TABLE_SCHEMA

AND c.CHARACTER_MAXIMUM_LENGTH > 0

FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 3, '')

FROM INFORMATION_SCHEMA.TABLES t

EDIT: missed the last line of code.

Sterling Ostin (10/21/2016)

---

Hanshi,

Thanks for your reply, but unfortunately its way over-complex. I found a way to do this that is simple and elegant.

1. Run SP_COLUMNS and direct output to a Temp table.

2. Take the output and hit the Temp Table to get just the Table and Columns I want.

3. Use that filtered output as the comparator against the client table and values I want to check.

Sweet, simple, easy and it works with just a few lines of code.

"A Backhoe is a powerful and capable machine, except when you are trying to plant a single flower. Don't use a backhoe when a small shovel will do better..."

Sterling Ostin (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

I did read Luis' post and incorporated it in an even more graceful and shorter solution that best suited my need.

Sterling Ostin (10/20/2016)

---

…

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 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.

Sterling Ostin (10/24/2016)

---

Jeff - Look at the fourth and sixth small paragraph of my original post. I did exactly what you are suggesting I did not do. I asked to get sp_column-style output.

Many replies on SSC ask what any given poster is "doing" - instead of focusing on what a poster wants to do. I need to get work done, not explain what our business does. And yes, this can be very frustrating - for that I apologize.

I know you are a SQL expert and I have seen your posts for years, however it might be good to remember that for many of us asking questions, SQL is a small part of what we do - as is the case in my work. I apologize for looking for quick help, but I thought that was at least part of the idea behind the SSC forums.