File links in the vision database

So I have a client who is asking me to change a server name inside all of their file links… as I’m looking at the tables with file links in them…

(here’s an easy way to do that):

select name from sysobjects where xtype = ‘u’ and name like ‘%filelinks%’
order by name


I notice that there are lots of weird file links in there, some with mapped drive letters like “J:”, “Y:”, “P:” etc… and tell my client that maybe she wants to look at the entire list before updating this server name.  Maybe there are some drive letters we want to replace with server names as well?


So what’s the easiest way to locate all tables with filelinks (one for each info center) and output a good list of who is linking to what?


This also answers a question that was asked of me today… “what is a good way to enumerate through a table of data without using a cursor?”  The answer is in this procedure I wrote for general consumption.

Cheers!

Loren

Here’s the proc:






create procedure z_admin_all_filelinks
AS
BEGIN


declare @sql varchar(8000), @tablename varchar(255)
set @tablename = ”

set @sql = ‘select * from ( 



set @tablename = (select top 1 name from sysobjects where name like ‘%filelinks%’ and xtype = ‘u’ and name > @tablename order by name)

while ISNULL(@tablename,”) ”
begin
set @sql = @sql + ‘select ”’ + @tablename + ”’ as ”Context”, max(createuser) as ”createuser”, filepath from ‘ + isnull(@tablename,”) + ‘ group by filepath ‘

set @tablename = (select top 1 name from sysobjects where name like ‘%filelinks%’ and xtype = ‘u’ and name > @tablename order by name)


if ISNULL(@tablename,”)!=” 
begin
set @sql = @sql + ‘
union all 

end

end

set @sql = @sql + ‘) a
group by context, createuser, filepath ‘

print @sql
execute(@sql)

END

Comments or questions for Loren?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s