Issue with Schema Removal from Table or view

Question

Issue with Schema Removal from Table or view

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
December 10, 2021 at 4:48 pm

#3962446

Example 1
declare @statement varchar(100) = '[dbo].[vw_EmployeeData]'
select @statement, CHARINDEX('.',@statement,6)
i want to remove schema dbo and bracketsfor schema and view name and i need the column data to be like this vw_EmployeeData
Example - 2
dbo.Employee
we have some rows with data with only schema with out brackets . i need to remove schema name and need data to be shown as only Employee

How to cast this?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

you can retrieve the object_id of that object and then get the name form sys.objects for the returned object.

as a string replacement alone - do a replace of both brackets by space and then do the charindex - but be aware that if someone created a schema with a "." on it your code will fail, and it will also fail for those strings where the schema was omitted or even for those where a server/database name is part of the string.

ScottPletcher SSC Guru Points: 101093 More actions · Answer 2

Use: SELECT PARSENAME(@statement, 1), viz.:

declare @statement varchar(100) = '[dbo].[vw_EmployeeData]'
SELECT PARSENAME(@statement, 1)

set @statement = 'dbo.Employee'
SELECT PARSENAME(@statement, 1)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Scott's method is the method I'd use to extract the object name.

I do concern myself what the source of the name is an how it will be used later in light of SQL Injection possibilities and I'll do things like what Frederico did to validate such things to ensure there's not a tick riding in the data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 4

I have another question, I have data with space before square brackets and after sqaure brackets when i run below example 1 statement returning NULL. But in example 2 We have space after closing square bracket. In both examples we are getting NULL. How to get the object name from these below 2 examples?

Example - 1

declare @statement varchar(100) = ' [dbo].[VW_EmployeeTech] '

SELECT PARSENAME(@statement, 1)

Example 2

declare @statement1 varchar(100) = '[dbo].[PeopleFeed] '

SELECT PARSENAME(@statement1, 1)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

That last bit is EASY to fix - trim your strings:

SELECT PARSENAME(TRIM(@statement), 1)

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

mcfarlandparkway wrote:

I have another question, I have data with space before square brackets and after sqaure brackets when i run below example 1 statement returning NULL. But in example 2 We have space after closing square bracket. In both examples we are getting NULL. How to get the object name from these below 2 examples?
Example - 1
declare @statement varchar(100) = ' [dbo].[VW_EmployeeTech] ' SELECT PARSENAME(@statement, 1)
Example 2 declare @statement1 varchar(100) = '[dbo].[PeopleFeed] ' SELECT PARSENAME(@statement1, 1)

Recommended reading:

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-ver15

Click on each function to learn what it does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101093 More actions · Answer 7

Interesting. I was not aware of that quirk of PARSENAME.

SELECT PARSENAME(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@statement, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))), 1)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".