Can I do this?

Question

Can I do this?

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Jeff Moden (10/26/2016)
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.
🙂
Heh... careful now. You don't have the time for this. Gotta trust me on this one. 🙂

I concur.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Jeff Moden (10/26/2016)
Heh... careful now. You don't have the time for this. Gotta trust me on this one. 🙂

Jeff, don't you think it's a bit too late to warn about the danger of minefields somebody who's already stepped on a landmine?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 3

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

Hi Sterling,

I’m afraid you’ve posted enough to let me to know everything I need to know about you.

And eliminate any opportunity of questioning that my acquired knowledge.

You say:

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

Let's see how truthful were you.

Below is the solution posted by Luis.

It’s the only solution he posted in this thread, so there is no doubt which solution was discussed:

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.

And this is what you posted as your “graceful and shorter solution that best suited my need”:

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

Further in the topic you posted a code snippet to show how did you implement the solution:

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

Obviously, there is not a trace of anything from Luis’s solution to be seen in here.

So, when you said

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

you simply lied.

You lied with no apparent reason to do so, it must be just out of habit.

You ignored (or did not understand, or did not bother to read – pick the option you prefer) all the good solutions offered to you – by Luis and others.

Luis even went an extra mile and developed the dynamic script which would do exactly what you’ve been asking for in you initial post:

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.

Instead of taking an effort on reading and understanding the suggestions from volunteers you get straight to expressing your disappointment with them not being servant enough to Your Greatness:

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.

To me (and replies from other community members suggest that it’s not only to me) in this set of posts you’ve been arrogant, impatient, rude, bully, not to forget – a liar.

All the grounds for this judgment are right here – I quoted your posts so you would not clear them up post factum.

Which would be an expected course of actions for a liar.

Sorry, but in just several posts you managed to expose your nature to everyone, and nobody appeared to be pleased with the show.

I bet anyone who’s unfortunate enough to work with you would confirm everything I “know” about you.

Send my best regards to them.

Hope never to hear from you again.

_____________
Code for TallyGenerator

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 4

All right, everyone. I think now is a good time to back away from this thread. Obviously there's been several misinterpretations of who has posted what and what those posts have meant. Feelings are running high and people are starting to feel insulted all around.

There's no need for this to become something nasty. So can we please just consider this thread closed and go back to business elsewhere?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

jfitch 84529 Valued Member Points: 57 More actions · Answer 5

We get data from multiple sources too and load them into a staging table - where we do our validations. The process it to first empty out the staging table from the previous run, then load the data into this table which is all large varchar - even numeric since we had to validate correct data types too. After errors are reported, either the only the clean data is loaded, with the erroneous rows needing to be loaded after they were re-submitted - or we'd do what you're doing and make it fit by truncating (depending on business rules).

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 6

Brandie Tarvin (10/27/2016)
All right, everyone. I think now is a good time to back away from this thread. Obviously there's been several misinterpretations of who has posted what and what those posts have meant. Feelings are running high and people are starting to feel insulted all around.
There's no need for this to become something nasty. So can we please just consider this thread closed and go back to business elsewhere?

Heh... dammit... I was going to get some popcorn and watch. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Jeff Moden (10/27/2016)
Brandie Tarvin (10/27/2016)
All right, everyone. I think now is a good time to back away from this thread. Obviously there's been several misinterpretations of who has posted what and what those posts have meant. Feelings are running high and people are starting to feel insulted all around.
There's no need for this to become something nasty. So can we please just consider this thread closed and go back to business elsewhere?
Heh... dammit... I was going to get some popcorn and watch. 😛

This could have become an thread like the Bankers Rounding threads!