April 30, 2008 at 4:24 am
Do you not just mark them for recompile?
April 30, 2008 at 5:27 am
All good, but in general, using Select * is a bad idea, even if you need all of the columns. Better to specify in case someone changes something in the underlying schema.
Using Select * can also burn you with views...
April 30, 2008 at 5:33 am
Ian Yates (4/29/2008)
No problem 🙂
Ian - in all the years that I've been using the query analyzer I never knew I could do that - if you have any more tricks up your sleeve, maybe you could compile them into an article and post it on this site...:)
**ASCII stupid question, get a stupid ANSI !!!**
April 30, 2008 at 5:34 am
Ian,
I too was going to post a script to do the job, but the drag/drop method is waaaaaaaaaaay better! 😎
Here it is anyway
create proc print_select_for
@table varchar(128)
as
begin
declare @s-2 varchar(max)
set @s-2=''
select @s-2=@s+case ordinal_position
when 1 then 'select '
else ', '
end+char(13)+' '+column_name
from information_schema.columns
where table_name=@table
order by ordinal_position
set @s-2=@s+char(13)+'from'+char(13)+' '+@table
print @s-2
end
go
exec print_select_for 'something'
Derek
April 30, 2008 at 6:54 am
I got tired of typing out the column names on large tables and simply bought a license of Redgate SQL Prompt and SQL Refactor. Both product provide for inputing the column names automatically. I was taught to never put SELECT * in any production code. Still feel that is good advice.
Tim
Denver, CO
April 30, 2008 at 7:27 am
The article is nice,
Is there any other way,
keeping the function same like tablea.* , can't we retrive the original values. like refreshing the function.
Can we refresh the function after adding table.
April 30, 2008 at 7:45 am
Stephen,
Interesting article. Thanks.
Ian,
I've been using QA for a long time and didn't know that trick. Thanks a bunch.
This is why I read the posts after an article.
April 30, 2008 at 8:01 am
Another alternative to drag-and-drop is click on word Columns then Ctrl+C to copy then in query window position your coursor where it should go and Ctrl+V to paste. This way you don't have to be as precise with your mouse and you can paste it in multiple places if you need to.
April 30, 2008 at 8:16 am
Since you want to select all, the lazy way to do it and remain itemized is to simply script table as select to clipboard and paste it in. This also works well if you're going to include most of the columns and just need to delete a few from the list. Otherwise as someone has already mentioned you can use the object explorer to drag out the names of there are only a few columns needed.
April 30, 2008 at 8:17 am
Great article.
Also, SQL Prompt is great. For those who haven't already tried it - go ahead, you'll be hooked. Note: I am just a happy user.
Briefly described, it is Intellisense for your database queries. The three features I'd hate to query without are:
1: It gives you column names and table names as you type. Two or three letters and a tab and you're done. Of course it needs to know which table(s) you are querying. Type the prefix and period, and it lists the columns for you to select. Or use ctrl+shift for a list of all.
2: Killer feature: Joins. You type "JO" - it shows JOIN, you TAB & space and it shows your most likely join targets. You TAB and it picks the (selected) join target and includes a default alias for it. You type "ON " and it shows the most likely expressions to use, you TAB. With these 9 keystrokes I routinely generate well over 50 characters of a SQL statement. I have a database where I routinely perform between 7-10 joins, and with SQL Prompt I can write it in seconds - and my typing is far from stellar. This feature alone has paid for my SQL Prompt license, probably several times over.
3: Want most columns and don't want to have to type - or even pick them all? Just type your query (joins and all) with SELECT * FROM , position your cursor behind the * and press TAB. Voila - you have a complete column list from all the tables in the join, prefixed with table aliases and ready for you to make any modifications you may want.
Tore Bostrup
April 30, 2008 at 8:28 am
All you needed to do was to recompile the function (or drop and re-create it)
Explanation: All compiled objects (Stored Procedures, Triggers, Functions, views, etc) are stored in the database in a separate area, and when you modify objects (i.e. table structure) used by these compiled objects they keep using 'last-know' structure until you update it (i.e. recompile).
To see dependency of objects you can use the stored procedure: sp_depends
A. to see all compiled objects which depend on a table:
sp_depends tablename
B. to see all objects (i.e tables, views, other SPs) which depend on a compiled object:
sp_depends compiled_objectname
April 30, 2008 at 8:50 am
Why can't we just recompile the function everytime we execute a DML Statement on one of the tables in the Return List?
April 30, 2008 at 8:53 am
I use a function and a view in tandem to handle lists of columns. This is useful because I don't have to remember the actual table name, I can just query the view where stName like '%someFragment%'
create schema mgmt
go
create function [mgmt].[ListColumns](@objectId int)
returns varchar(max)
as
begin
declare @list varchar(max)
set @list = '';
;with cols as
(
select
c.name as cName,
row_number() over (order by c.column_id) as rowNum
from sys.columns as c
where c.object_id = @objectId
)
select
@list = @list +
case rowNum
when 1 then '[' + cname + ']'
else ', [' + cname + ']'
end
from cols
return (@list);
end
go
create view [mgmt].[cols]
as
select
stName = s.name + '.' + o.name,
o.object_id,
mgmt.ListColumns(o.object_id) as columnList
from sys.objects as o join sys.schemas as s on o.schema_id = s.schema_id
where type in ('u', 'v')
go
Really I should just case the rowNum instead of defining the CTE twice, but it gets the job done anyhow.
EDIT - the case thing was bothering me so I updated the function to use it.
April 30, 2008 at 9:22 am
Great article. We have all heard not to use SELECT *, but this is a concrete example of why. I generally stay away from SELECT * just for form, but now I can see how it can truly have disastrous consequences.
I also great appreciated everyone chiming in with their tips. Both the tip to drag the columns folder and the tip to use the script feature were perfect. It was also fun to see people's scripts.
April 30, 2008 at 9:28 am
I ran into this same issue with views a few years back. Basically it does not recache the schema of the view without it being rebuilt so that the data appears in the same order as the columns in the table, without consideration that a column was added, so the names don't always match up, and there are no additional columns added to the view. I agree that it is good practice to always use table names in views and functions to avoid this problem, especially when there a many of them. There are a couple of ways to do this that work well, you can either use SQL Prompt(an awesome tool) and tab right after the * which breaks out the list of fields, or you can right click the table in the object explorer and create a select script which in turn creates a select statement with the field broken out. I did not know about dragging the columns folder.
Viewing 15 posts - 16 through 30 (of 151 total)
You must be logged in to reply to this topic. Login to reply