Hi all,
As the title implies I want the characters to the right of "dbo.", which - you guessed it - are tablenames, but of course I need to experiment with the length as some will be 5 characters long, others 50.
Of course I had
Select Tablename
From ListOfTables
Where Tablename like'%dbo.%'
But this doesn't come close.
TIA
January 5, 2022 at 4:26 pm
You are matching things that might be
- dbo.mytable
- etl.dbo.table
I'd use
select TableName
from ListofTables
where Left(TableName, 4) = 'dbo.'
Repro:
WITH ListofTables (TableName)
AS ( SELECT s.name + '.' + o.name AS TableName
FROM
sys.objects o
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE type = 'U')
SELECT TableName FROM ListofTables WHERE LEFT(TableName, 4) = 'dbo.';
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 5, 2022 at 4:43 pm
If you just want table names that start with 'dbo.', then you can do this:
SELECT STUFF(Tablename, 1, 4, '') AS Tablename
FROM dbo.ListOfTables
WHERE Tablename LIKE 'dbo.%' /*allows an index seek on Tablename, if available*/
Are there multiple tables names in one row? Is that why you included a '%' in the front of the string?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 5, 2022 at 4:54 pm
Neither set of code is coming up with a meaningful result set, not sure where the disconnect here is, but I'll try again:
If a row contains:
koagvakjnonsenseajg4648.dbo.RandomTableForSSChahfhaijfhfhf5664646
I want to return the dbo. string PLUS any x characters to the right of said string, so I need to be able to specify the number of characters after dbo. that are returned. In the above case the 'RandomTableForSSC' tablename has 17 characters, I'd start with 17 and try some larger numbers, I don't mind as the data doesn't have to be cleansed.
declare @t table (tablename varchar(100))
insert into @t values
('dbo.mytable'),
('etl.dbo.table'),
('koagvakjnonsenseajg4648.dbo.RandomTableForSSChahfhaijfhfhf5664646'),
('nonsensetable')
select tablename,
Substring(tablename, charIndex('dbo.',tablename), len(tablename))
from @t
where charIndex('dbo.',tablename) > 0
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 5, 2022 at 5:23 pm
SELECT SUBSTRING(TableName, CHARINDEX('dbo.', TableName), 8000) AS TableName
FROM dbo.whatever
WHERE TableName LIKE '%dbo.%'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 5, 2022 at 6:09 pm
I marked Mike's as the script that satisfied my requirement but to be fair Scott's came closest - I can't however changed the marked answer.
Thank you gentlemen 🙂
January 5, 2022 at 10:16 pm
Neither set of code is coming up with a meaningful result set, not sure where the disconnect here is, but I'll try again:
If a row contains: koagvakjnonsenseajg4648.dbo.RandomTableForSSChahfhaijfhfhf5664646
I want to return the dbo. string PLUS any x characters to the right of said string, so I need to be able to specify the number of characters after dbo. that are returned. In the above case the 'RandomTableForSSC' tablename has 17 characters, I'd start with 17 and try some larger numbers, I don't mind as the data doesn't have to be cleansed.
Does the table name exist in a database anywhere that's accessible??
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply