April 29, 2008 at 9:29 pm
Comments posted to this topic are about the item A Function Gotcha with the Use of an Asterisk
April 29, 2008 at 10:13 pm
I simply do a "select top 1 * from table" and return the results in text. Then copy the first row which is the fieldnames, paste it into the query window and do 2 "find > replaces". First replace all spaces with double commas and then replace all double commas with single comma. This gives me a list of the fields seperated by a comma.
just a suggestion...
April 29, 2008 at 10:52 pm
I use the following easy-to-memorize SQL to get column lists:
select ', ' + name from syscolumns where id = object_id( 'MyTable' ) order by colorder
or
select ', ' + column_name from information_schema.columns where table_name = 'MyTable' order by ordinal_position
April 29, 2008 at 11:16 pm
Great article, except that you (and the other posters) either don't use Query Analyzer or SSMS to write your queries, or you do and aren't aware of a neat trick...
In either tool you have a query open where you want the column names of a table to appear. You simply navigate to the table using the tree on the left of the screen. Then expand the + sign next to the table so that you have the usual Columns, Indexes, etc folder headings beneath the table. You can drag and drop the "Columns" folder to your query and PRESTO, the column names appear. MUCH easier 😛
You can also drag & drop the table name for long names - it will even prefix with dbo. (or other schema if not dbo obviously).
Since I've been using the drag & drop technique I have always steered clear of the select * but I can understand using the shorthand if you weren't aware of drag/drop.
Anyhow, good description of the problem. I wonder if you happened to have a string field push over into an integer return column if you might have noticed the problem sooner (because the string wouldn't go into an int column...).
April 29, 2008 at 11:26 pm
Thats a neat trick Ian. Where did you get it from ?
"Keep Trying"
April 29, 2008 at 11:26 pm
For a few fields, drag and drop is fine but if you have a lot of fields and want all of them you have to drag, drop and then click off the field name to add a comma. Then drag, drop etc, etc
Actually for a few bucks I now use SQL prompt unless I need the entire table fields.
April 29, 2008 at 11:30 pm
Leon, I think you misunderstood. Please try it - if you drag "Columns", not an individual field itself, then you'll get them all transferred. Each is separated by a comma. Only thing you may wish to do is put a few line breaks in every third or forth field I suppose. Using Ctrl+ArrowKeys and your enter key makes short work of that.
April 29, 2008 at 11:34 pm
Thx Ian - I understand now. I have never used that - simple things you somehow miss on the way... :w00t:
April 29, 2008 at 11:36 pm
Another way of getting a select query quickly using Query Analyzer is also to use the Object browser window on the left, right click on a table name, and select one of the "Script Object to..." options. For a select query, I usually use "Script Object to Clipboard As" then choose "Select" from the drop-down menu, then paste the query to my query window. This method includes "[" and "]" around each object name, plus the "dbo." in the table name.
Mal
April 29, 2008 at 11:37 pm
No problem 🙂
April 30, 2008 at 1:19 am
Thanks for the explanation. Since you explained the root cause, I'll be even more motivated to list al the columns by name.
By the way, I just use the ms SQL server management studio to create these queries : right-click on the table and select "Script table as" --> SELECT to --> new query window or the clipboard.
It lists the columns nicely, so you can delete those you don't want 🙂
e.g.
SELECT [LOG_id]
,[LOG_PartNum]
,[LOG_AgileAccount]
,[LOG_DateTime]
,[LOG_ehub]
,[LOG_IPAdress]
,[LOG_msg]
FROM [dbDocCreator_Autonumbering].[dbo].[tblLogUsage]
Also very nice to quickly create INSERT, UPDATE queries.
Have a nice day
April 30, 2008 at 1:27 am
This problem also arises with views. If you do "select * from" within a view, you will get the column mismatching as well.
The select will fail if you have cut a column off your table, but inserted columns will offset data as shown.
April 30, 2008 at 1:33 am
Neet trick Ian!
I quickly made a script to return columns for a table but your trick was way cooler. 🙂
But if you're somewhere in a deserted island without a nifty GUI some thing like this might help.
CREATE procedure util_GetColumnNames
(
@TableName varchar (100),
@Delimiter varchar (10) = ',',
@AsRow bit = 0
)
as
if not exists(select 1 from sys.objects where type = 'U' and Name = @TableName)
print 'Table does not exist.'
else
if (@AsRow = 1)
begin
declare @cols varchar(2000)
SELECT @cols = ISNULL(@cols + @Delimiter + '[' + C.Name + ']',
'[' + C.Name + ']')
FROM sys.objects O
inner join sys.columns C
ON O.object_id = C.object_id
where type = 'U'
and O.Name = @TableName
SELECT @cols
end
else
select '[' + C.Name + ']' + @Delimiter
from sys.objects O
inner join sys.columns C
ON O.object_id = C.object_id
where type = 'U'
and O.Name = @TableName
This still returns a trailing delimiter in the last row, but Im not bothering to fix it.
/Martin
April 30, 2008 at 2:13 am
Does anybody know if this (the original '*' gotcha) happens with nested functions.
I have a set of functions that each use expicit field names to query from tables (Coercing the fields to the same set of names in each query), but then an overarching function that queries '*'from each sub-function. If I changed the selections in sub-functions would I need to rebuild the overarching function?
April 30, 2008 at 3:38 am
I can recommend the suggestion of Ian to work in SQL Query analyzer and use drag and drop. It helped me a lot.
Thanks for the great explanation of this problem. You never know were the problem can cross your path. I will store it in my memory.
Menno van Leewen
Viewing 15 posts - 1 through 15 (of 151 total)
You must be logged in to reply to this topic. Login to reply