Convert [dbo].[tablename] to just tablename? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert [dbo].[tablename] to just tablename?

Author

Topic

lgoodbar
Starting Member

5 Posts

Posted - 2008-09-17 : 14:43:47

The sp_msforeachtable stored procedure passes along table names in the format [schema].[table]. If I wanted to use those results to query sysobjects or other per-table operation that does not require the schema, is there a way to convert [schema].[table] to just table?

I realize a loop through sysobjects can achieve the same thing but we use sp_msforeachtable and sp_msforeachdatabase pretty heavily and like the code reduction.

TIA,
Loyd

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-17 : 14:47:01

Didn't get it? Can you explain clearly?

lgoodbar
Starting Member

5 Posts

Posted - 2008-09-17 : 14:53:55

The output from sp_msforeachtable looks like this:
exec sp_msforeachtable 'print ''?'''

[dbo].[tblVBSSweepData]
[dbo].[tbl_VBSRawSweep]

I would like to see:

tblVBSSweepData
tbl_VBSRawSweep

We have some procedures that expect just the table name.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-17 : 14:59:02

How will you identify if tablenames are same but schema is different?

select name from sys.objects
where type ='U'

lgoodbar
Starting Member

5 Posts

Posted - 2008-09-17 : 15:23:42

OK.
create table [ETS\LGOODBAR].[tblVBSSweepData] (id int);
select name, uid from sysobjects where xtype ='U';

name uid
---------------- ------
tblVBSSweepData 1
tblVBSSweepData 11
tbl_VBSRawSweep 1

select uid, name from sysusers where uid in (select uid from sysobjects where xtype='U');

uid name
------ -------------
1 dbo
11 ETS\LGOODBAR

Question remains, what is the best way to accomplish this? I was hoping there was a function to split out the schema name and table name.

You can't use the results of sp_msforeachtable directly in these type queries.
select name from sysobjects where name='[dbo].[tblVBSSweepData]' and xtype='U';

The query must do something like this:
select name from sysobjects where name='tblVBSSweepData' and xtype='U' and uid = (select uid from sysusers where name='dbo');

Still need to split schema and table... Regular expressions seem like a way to go.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-17 : 15:38:47

Are you looking for this one?

select ss.name from sys.objects ss
inner join sys.schemas sa
on sa.schema_id = ss.schema_id
where ss.type = 'U' and sa.name ='Yourrequiredschema'

lgoodbar
Starting Member

5 Posts

Posted - 2008-09-17 : 15:56:17

Perhaps I'm not explaining clearly enough.

I have an existing stored procedure FOO, which accepts a table name. Let's disregard multiple schemas in a database for a moment; I'd hazard to say the majority of installations don't take advantage of it.

I want to run stored procedure FOO over every user table in a database. The first tool that comes to mind is sp_msforeachtable because it already does the table selection (excludes system tables) and looping logic for me.

So I would code:

exec sp_msforeachtable 'exec foo ''?'''

That doesn't work because this is what is executed:

exec foo '[dbo].[tbl_VBSRawSweep]'
exec foo '[dbo].[tblVBSSweepData]'

FOO just expects the simple table name:

exec foo 'tbl_VBSRawSweep'
exec foo 'tblVBSSweepData'

Without reinventing the sp_msforeachtable wheel (declare cursor for... select... loop...) I was hoping to call a function that would take [dbo].[tblVBSSweepData] and return tblVBSSweepData.

The multi-schema issue is legitimate but not applicable in my environment.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-17 : 16:02:14

[code]select TableName = parsename('[dbo].[tbl_VBSRawSweep]',1)[/code]
Results:
[code]TableName
----------------------------------------
tbl_VBSRawSweep

(1 row(s) affected)[/code]

CODO ERGO SUM

lgoodbar
Starting Member

5 Posts

Posted - 2008-09-18 : 08:11:29

Thanks Michael, that's exactly what I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources