SQL Experts - Tables Names Question

PirateJon

Follow-up from a previous SQL question - Invalid SQL query/view issue.

When last we met our intrepid hero on his journey to the mythical kingdom of "Stable App", he was deep in the untamed jungle running from invalid CF queries... now he faces his biggest challenge yet - the hall of invalid table names!

I've imported a bunch (few hundred tables, each with 1 to hundreds of columns) of data into SQL 2008. Apparently because of the horrid way we had to migrate data, some of the column names may now have trailing spaces. This was causing my view to fail. Removing the space fixed the issue.

Yes, I'm aware SQL 2008 says it ignores trailing spaces in column names. It might, but coldfusion flips its shit and errors out.

Oh lords of the Query - Is there a way to check for trailing spaces in tables names without manually checking each one?

Deebaser

PirateJon wrote: »

Follow-up from a previous SQL question - Invalid SQL query/view issue.

When last we met our intrepid hero on his journey to the mythical kingdom of "Stable App", he was deep in the untamed jungle running from invalid CF queries... now he faces his biggest challenge yet - the hall of invalid table names!

I've imported a bunch (few hundred tables, each with 1 to hundreds of columns) of data into SQL 2008.

o_O
This isn't directed at you per se, but to the person that designed your database


UR DOIN IT WRONG

Deebaser

Load it into Acess and TRIM the shit out of it.

JoeUser

Try this:

select * from sys.tables
where name like '% '

vonPoonBurGer

I think he's looking for columns with trailing spaces in the name, not tables.

In Oracle I'd use the DBA_TAB_COLUMNS view to do this. For MS SQL Server 2008, a combination of the syscolumns and sysobjects views should provide comparable info. You can run a query against a join of those views to see if any columns have trailing spaces in the name:

SELECT o.name AS "Table Name", c.name AS "Column Name"
FROM sys.sysobjects o, sys.syscolumns c
WHERE o.id = c.id
AND o.xtype = 'U'
AND RTRIM(c.name) <> c.name;

That should list all columns that have trailing spaces in the column name, as well as the table each one is in. Those views may exist in some other schema than SYS, I don't know if the name of the user that owns those views can vary in SQL Server 2008.

If you need to check for leading and trailing spaces in column names, you can use LTRIM(RTRIM(c.name)).

PirateJon

I think he's looking for columns with trailing spaces in the name, not tables.

Correct.

I'm going to play with that code. Thanks a ton.

Lewisham

Deebaser wrote: »

o_O
This isn't directed at you per se, but to the person that designed your database


UR DOIN IT WRONG

Yeah, this is the second "How the hell did this happen?" question from you.

Either you need to fire the DBA you have, or hire one because you don't have one. Either way, these problems should not be happening. Like, ever.

PirateJon

vonPoonBurGer wrote: »

SELECT o.name AS "Table Name", c.name AS "Column Name"
FROM sys.sysobjects o, sys.syscolumns c
WHERE o.id = c.id
AND o.xtype = 'U'
AND RTRIM(c.name) <> c.name;

That works except for the RTRIM. I can take out that last line and get enough to work with, but it seems very close. I've read SQL 2k8 doesn't care about trailing spaces, so I might have to do this manually with the results I get (which are super-fucking helpful, thank you. I owe you a beer).

Either you need to fire the DBA you have, or hire one because you don't have one. Either way, these problems should not be happening. Like, ever.

Dude if I could, I so would. Hiring takes ~6 months to a year. This systems goes live inside two weeks. o_O

"other duties as assigned", you know the drill. Shit I'm a networking guy anyway. Always hated doing server admin. This is why. :x

rfalias

Would it be possible to RTRIM in coldfusion instead?
I know little of ye colde fusione

vonPoonBurGer

PirateJon wrote: »

That works except for the RTRIM.

RTRIM should exist in SQL Server 2008. Is it an error related to data types? I notice the syscolumns views uses a data type called sysname. You might need to explicitly CAST that to a varchar so RTRIM can operate on it, as follows:

AND RTRIM(CAST(c.name AS varchar)) <> c.name;

If that still doesn't work, you can try replacing the RTRIM bit with a LIKE, as in JoeUser's query for tables:

AND c.name LIKE '% ';

You may have to use CAST for either method, I don't have access to a SQL Server 2008 system so I can't say for sure.

PirateJon

RTRIM exists, but it returns 0 rows. Shit is weird, SQL queries work with or without the trailing spaces, which is why it took so long to figure out WTF was going on. SQL tables has data, query works fine in mgmt studio, fails on the CF page. o_O

I'm thinking that if SQL 2k8 ignores the trailing spaces in column names like they say, then maybe both fields would parse the same. Thus no rows... but that makes no sense to me. Maybe there's a checkbox for "fuck with the server admin lol" in here somewhere.



Anyway the "AND c.name LIKE '% ': worked like a champ. Got a nice short list with all the bad column names. Thank you guys!