April 30, 2008 at 9:48 am
sammesel (4/30/2008)
To see dependency of objects you can use the stored procedure: sp_dependsA. 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
Except, of course, that this doesn't actually work!
Try the following:
create proc a as print 'a'
go
create proc b as exec a
go
exec sp_depends 'a'
exec sp_depends 'b'
go
drop proc a
go
create proc a as print 'a-prime'
go
exec sp_depends 'a'
exec sp_depends 'b'
exec b
The output I get, which I expect everyone will get is:
In the current database, the specified object is referenced by the following:
name type
------- ----------------
dbo.b stored procedure
In the current database, the specified object references the following:
name type updated selected column
------- ---------------- ------- -------- ------
dbo.a stored procedure no no NULL
Object does not reference any object, and no objects reference it.
Object does not reference any object, and no objects reference it.
a-prime
Procedure b depends on procedure a, but sp_depends fails to report this.
Derek
April 30, 2008 at 10:00 am
A somewhat tangential issue is to qualify all of your columns with a table prefix whenever there is more than one table involved. For one I think you save a tiny bit of perf in that code doesn't have to figure out which table each column belongs to, but it also prevents ambiguous column errors should a column later be added. Consider:
create table t1 (pk int, i1 int)
create table t2 (fk int, i2 int)
go
create view v1
as
select i1, i2 from t1 join t2 on t1.pk=t2.fk
go
select * from v1
go
This works fine until you do
alter table t1 add i2 int
go
select * from v1
go
at which point the select gives you:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'i2'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'v1' because of binding errors.
So it is preferable to always preference the columns with the table name/prefix like
select t1.i1, t2.i2 from t1 join t2 on t1.pk=t2.fk
April 30, 2008 at 11:00 am
Doing "select *" effects to view as well. Check out the view before and after the schema change to table A.
CREATE VIEW TestView
AS
Select TableA.* -- Note: All fields selected from table A
, TableB.fieldA
, TableB.fieldB
, TableB.fieldC
from dbo.TableA as TableA
inner join dbo.TableB as TableB
on TableA.recordID = TableB.recordID
GO
select * from TestView
GO
Drop view TestView
April 30, 2008 at 11:46 am
Yup I ran into this for some views that i had that did a "Select * from table"
However for views, you can run a stored procedure "Sp_refreshView" that will rebuild the column set.
Check out the link http://msdn.microsoft.com/en-us/library/ms187821.aspx
This link decribes how to use "Sp_refreshView" and it also has a script to refresh all views.
Does anyone know if a similar sp exists for Functions?
April 30, 2008 at 12:17 pm
If I am altering the structure of a table then at the end of my script I put
exec sp_recompile ' '
UPDATE STATISTICS
If it is a smallish table then I also use DBCC DBREINDEX
The only time I use SELECT * is if I am creating a quick backup table of a few select records
SELECT *
INTO DBA.dbo. _backup
FROM
WHERE
April 30, 2008 at 12:26 pm
sp_recompile does NOT recompile UDF's. I'm not sure about views, but that should be easy to test.
Tore Bostrup
April 30, 2008 at 12:46 pm
try to create a SP that actually does something with a table.
and then execute: sp_depends to that SP or to that table, that was my point.
so you'd use the results from sp_depends to review all those objects. Ideally you'd annotate everything that uses the table, then you modify the table and review all compiled objects. Hopefully you have all scripts in a safe place
April 30, 2008 at 1:00 pm
The same problem impacts view (at least in 2000) - they get corrupted unless recompiled. The erroneous results are spectacular (dates under numeric column headers, etc.)
April 30, 2008 at 2:12 pm
Thanks Leon, Richard, Ian, Malcolm, Martin, Derek, Tim, Aleksandr, Margarity, and Adrian for you methods on bringing in a file list. Ian especially as this trick is neat and I'm sure many of us Query Analyzer users were unaware of it. Tim's advice never to use select * is well worth considering. Carl, thanks for the direction on using ms SQL server management studio, I don't have that but your comments should help those that do. Kll, thanks for highlighting the issue also effects views also backed up by Thomas and several others.
Tony.dyer, I'm suggesting that yes you would need to rebuild the overarching function, as the same principle applies, the columns list is built when it is compiled. Naresh, the way to refresh the function is by recreating it, but if you have used select * prolifically, you may have problems finding them all quick enough not to cause problems, better not to use select * I think.
Tore you comments on SQL Prompt, I'm guessing this needs your database to have its relationships defined, sadly my supplier didn't supply that, but it sounds very useful, thanks. For those that don't know SQL Prompt is a product by Redgate.
Sammesel, thanks for raising the use of sp_depends, I've looked at this before but found it short, since my functions are stored in a separate database to the data, and it doesn't work across databases (well not so as I've seen), and Derek thanks also for showing some issues with this. Harshil_sutaria, yes we could just recompile, but by being specific about fields, and using Ian's suggestion to make this easy, we save a lot of trouble and remove risk.
Adrian, another great tip, and something I try to always do, the prefixing of column names. We abbreviate all our prefixes, based on part of the table name, which is quicker than typing the whole and still easy to identify, at least a lot easier than the prefixes some use, of a, b, c, etc, which means you still have to look through your join logic to see which table your prefix is for. Luckily our tables allow a standard set of easy to identify prefixes, you may not be so lucky of course.
Matteo, thanks for the comments on sp_refreshView, I've not used views myself, but I'm sure this will come in handy at some point. David, you update statistics is good advice, I've seen a database application die because someone had stopped refreshing statistics, soon was fixed, but didn't look good.
To all, thanks for your comments regardless, as we all value from your input and it is much appreciated.
April 30, 2008 at 2:24 pm
Actually, SQL Prompt uses column names to produce the ON suggestions, but if you don't have the relationships defined, you'll have to select the table to join to - but again, a few keys and the intellisense will help in that. Try the free (time-limited) version!
Tore Bostrup
April 30, 2008 at 4:02 pm
Great findings out about Function.
Excellent.
Thanks
Naras.
April 30, 2008 at 10:05 pm
I get the list of columns using an SP. This simple SP has multiple uses. In this one can choose the delimiter like a comma or a + sign or nothing. The SP will return the list of column. Create the SP in Master DB and you can get the columns of any table in the current DB. The SP is given below.
The SP:
create Procedure [dbo].[sp_tablecolumns]
/* Param List */
(@tablename varchar(50) = null, @delimeter char(1) = null )
AS
/******************************************************************************
** File: sp_tablecolumns
** Name: sp_tablecolumns
** Desc: Displays the columns in a specified table in the current DB
**
**
**
**
**
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
**
**
**
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -----------------
*******************************************************************************/
declare @sql varchar(1000),@delim char(6)
set nocount on
if left(@tablename,1) = '#'
begin
set @delim = ' like '
set @tablename = rtrim(@tablename)+'%'
end
else
set @delim = ' = '
if @tablename is null
print 'Execute this sp in the current database and specify the table name for which columns list is needed.'
if @delimeter is null
begin
set @sql = 'select sc.name from sysobjects so, syscolumns sc '
set @sql = @sql + ' where so.id = sc.id and so.name'+@delim
set @sql = @sql + ''''+@tablename+''''+' order by sc.colorder'
exec (@sql)
if @@error <> 0
print 'Table is not in current database.'
end
else
begin
if @delimeter = ',' or @delimeter = '+'
begin
set @sql = 'select sc.name '+'+'+''''+@delimeter+''''+' from sysobjects so, syscolumns sc '
set @sql = @sql + ' where so.id = sc.id and so.name' + @delim
set @sql = @sql + ''''+@tablename+''''+' order by sc.colorder'
exec (@sql)
if @@rowcount = 0
print 'Table is not in current database.'
end
else
print 'Expected delimeters are "," or "+" only.'
end
April 30, 2008 at 11:28 pm
Great one. Wonderful findings. Also the trick of Ian was good though I also used to do the same. I found it accidently.
🙂
May 1, 2008 at 2:49 pm
Interesting timing on this article. We ran into almost this exact same problem yesterday, only with views. We have a number of views that have been created that utilize the wild card character for all fields in a table. Yesterday, when we added another column to this table, the view got all messed up. If only I had have read this article 2 days ago, I would have saved myself some time in trying to trace the source of the problem I was having with views, views based on views, and reports based on views based on views....
May 7, 2008 at 4:37 am
In Query Analyzer (or SSMS 2005 Query window) you can use Options from the Tools menu for the format of "Script Object to...".
In Options on Script-tab you can set "Identifier Limiter".
The setting of "Qualify object name by its Owner" doesn't seem to have effect on the format of data manipulation statements in Query Analyzer.
Viewing 15 posts - 31 through 45 (of 151 total)
You must be logged in to reply to this topic. Login to reply