|
|
From a colleague in our release team:
Do we have any field in the live database that contains "G:"?
The database has approximately 600 tables, each having an average of about 25 columns, and the data stretches to about 3TB at the moment.
WTF!
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Reply "Yes. But it's in an encrypted password"
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
|
That will show you if there is a column of a particular name - what he was asking was "is there any column in any table on the database that contains "G:" in the data
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Oh, well, from top of my head, I would just select all the tables, all the columns and would loop through them with a select query
modified 20-Oct-19 21:02pm.
|
|
|
|
|
..loop through three terrabytes worth? 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Yes, with a cursor that locks each table, to prevent anyone inserting the G: value after you already checked. It is the only exhaustive, thread-safe, conclusive, and accurate way to know!
A better method by be to ask "why".
|
|
|
|
|
The why is obvious, it's a nonsense request. Must be something clever from a managers' point of view.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
What I'm saying is this nonsense request could have an underlying question that wasn't stated, and could possibly be answered in a very simple and easy way... which would be a win for the one asking... and educational in a way that prevents stupid questions from coming up again, which anger and derail the developers.
|
|
|
|
|
Or worse, a clever programmer!
if (data.Contains("G:"))
{
}
|
|
|
|
|
Nope, sys.tables gives you the tables, sys.colums gives you the columns and sys.types gives the data types.
You have to execute select statements on text type columns. You would have around 1000 select statements to loop through, not the actual data
modified 20-Oct-19 21:02pm.
|
|
|
|
|
You'd be looping all text-columns and memo-fields (up to 2Gb potentially), within all tables. That's two loops, continously crunching on the DB-server. To find a two-character string?
The only correct answer can be that there'd better be a friggin' good reason for the request, and to request what the elephant they were doing so you can write a more specific query. I doubt that the two characters could hide 'anywhere'.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hey, if his live depends on it, I'm suggesting a solution. Actually it's not that bad.
you can:
select TableName, columnName from whatever joins you need to do on all columns that are text, varchar, nchar etc.
Then you run
select count(ColumnName) from TableName where columName like '%whatever you search%'
Let's say 1-2 secs per query on a table up to 1 million records, he will have the answers in a hour or two.
It's a ridiculous request, but you know, if he absolutely needs to do it ...
modified 20-Oct-19 21:02pm.
|
|
|
|
|
Bad Hombre wrote: Actually it's not that bad.
Agreed, it is not 'that bad', but it is absolutely not what I want to hear from a specialist. Given the amount of data, and the type of request, and given that you have the freedom to make better suggestions, I'd expect one.
Any decent database-operator will have a backup of anything on that server. Go search that and leave the production database alone.
Ask where the customer "lost his G:", on which page, which application. Ask for a date-range. When did you have your G:? Ask whether it is actually feasible - in a database full with blobs you're bound to run into that combination, how do you know if it is the G: that the client is looking for, or just a random G:? Could it be in an encrypted or compacted field, and if so, do you want to search those too? Do you seriously need to search usernames and hash-columns, any logging-tables, if the customer cannot have lost his G: there?
Bad Hombre wrote: It's a ridiculous request, but you know, if he absolutely needs to do it ...
Instead of doing something rediculous because you're simply told to do so, you could try and recognize a failure in communication and offer an intelligent alternative.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I said text columns, not varbinary. This pretty much excludes blobs, also you can exclude columns with max_length let's say 2000 characters.
Edit: And I'm not a database operator, administrator or anything like that, just a lowly mobile developer, so I don't insist on having the perfect solution.
modified 20-Oct-19 21:02pm.
|
|
|
|
|
Bad Hombre wrote: I said text columns, not varbinary. This pretty much excludes blobs, also you can exclude columns with max_length let's say 2000 characters.
You might be choosing to actually exclude the columns that are actually required. Without asking, one is just guessing.
Bad Hombre wrote: Edit: And I'm not a database operator, administrator or anything like that, just a lowly mobile developer, so I don't insist on having the perfect solution.
Sometimes any solution is better than having nothing. There's no ranks here, so 'lowly' does not apply
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
No, write the query, and send it to him to execute... 
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
The easiest way to solve this is to insert a new row into a table and have G: as part of the content for a column's data. Now answer "yes" and if he asks for the data, just send him the row you just inserted.
You are welcome. 
|
|
|
|
|
Genius!
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Exactly!! 
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
This guy knows what's up... 
|
|
|
|
|
Elegance is simplicity. The perfect solution.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
|
I have to side with Wally.
You cannot start normalization without knowing the structure; all the fields need to be known.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
