XML FeedsColumn names (and table names) should not have spaces or any other invalid characters in them. This is considered bad practice because it requires you to use square brackets around your names. Square brackets make the code harder to read and understand. The query (presented below) will also highlight columns and tables with numbers in the names. Most of the time, when there is a number in a column name, it represents a de-normalized database. There are exceptions to this rule, so not all occurrences of this problem need to be fixed.
Based on a comment from Aaron Bertrand, I decided to modify the code below. I recognize that some organizations allow (and may even encourage) the use of the underscore character. In the newly modified code below, you can include a list of acceptable symbols. The code below allows the underscore symbol and the $ symbol. Modify this local variable to include any symbol that is acceptable within your organization.
How to detect this problem:
- Declare @AcceptableSymbols VarChar(100)
- Set @AcceptableSymbols = '_$'
- SELECT 'ColumnName' AS Type,
- Table_Name + '.' + Column_Name AS Problem
- FROM Information_Schema.Columns
- WHERE Column_Name like '%[^a-z' + @AcceptableSymbols + ']%'
- UNION all
- SELECT 'TableName', Table_Name
- FROM Information_Schema.Tables
- WHERE Table_Name Like '%[^a-z' + @AcceptableSymbols + ']%'
How to correct it: If this is a number issue, you may need to redesign your database structure to include more tables. For example, if you have a StudentGrade table with (StudentId, Grade1, Grade2, Grade3, Grade4) you should change it to be StudentGrade with (StudentId, Grade, Identifier). Each student would have multiple rows in this table (one for each grade). You would need to add an identifier column to indicate what the grade is for (test on November 10, book report, etc).
If this is a weird character issue, then you should change the name of the column so it is a simple word or phrase without any spaces, numbers, or symbols. When you do this, make sure you check all occurrences of where this is used from. This could include procedures, function, views, indexes, front end code, etc...
Level of severity: mild
Level of difficulty: moderate
I keep "weird" characters out of "normal" table names that will go to production. This lets me reserve such characters for use in table names that are for development/test use only and should not go to production databases. My favorite weird character is the dollar sign because it really stands out... For example, I might name a table of development or test results "_$Test$Benchmark$Run1" 
George, your check for an "invalid column name" is a little strict. What about underscores? Just because spaces and dashes are bad characters for a column name does not make all symbols taboo. 
Aaron, I dislike any symbols in names. This is my preference, but I can see where different organizations would allow certain symbols in their names. I will modify the code (later today) so that it allows a list of acceptable symbols. 
George, if someone does the totally crazy and says ']' is an acceptable character, the code will break. 
What about - in table names? 
Naomi, table names or actually any object name (proc, database, function, view etc etc) should all follow the same kind of naming conventions 
I do not agree with marking numbers as not allowed in column names as there are a lot of situations where column name contains number inside - of course not as leading character. 
Hello,
Columns such as ICD9, CO2E_Value, and even Zip5 are valid column names that do not signify unnormalized data.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.