June 6, 2011 at 1:56 pm
is there an easy way to split a fully qualified name into schema and tablename?
My issue is the name could come as:
Discount
dbo.Discount
DB4.dbo.Discount
but I need to use the table name only or table name and schema in my query.
Thanks,
Tom
June 6, 2011 at 2:17 pm
yep there's a built in function to help;
PARSENAME; give it a try, here's an example:
SELECT
PARSENAME(TheString,4),
PARSENAME(TheString,3),
PARSENAME(TheString,2),
PARSENAME(TheString,1)
FROM
(SELECT '192.168.1.55' AS TheString UNION ALL
SELECT 'Discount' UNION ALL
SELECT 'dbo.Discount' UNION ALL
SELECT 'DB4.dbo.Discount' UNION ALL
SELECT 'ServerName.DB4.dbo.Discount')X
Lowell
June 6, 2011 at 2:18 pm
Not really sure what you are asking. If you do not specify the server, database, or schema parts of an object's 4-part identifier, SQL Server will use the defaults.
Example:
SELECT * FROM Products
might actually be interpreted by SQL Server as:
SELECT * FROM [LOCALHOST].[Northwind].[dbo].[Products].
_________________________________
seth delconte
http://sqlkeys.com
June 6, 2011 at 2:28 pm
The reason I am asking is that I am using the following query:
SELECT @IsIdentity = sys.columns.is_identity
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.is_identity = 1 AND
sys.objects.type IN ( N'U' )
AND sys.objects.name = 'Roles'
AND SCHEMA_NAME(schema_id) = 'intr'
But the table name will come as a parameter:
@TableName = 'intr.Roles'.
I want to put this into the select statement.
But since the @TableName will come in one of three ways as mentioned above.
So I need to split out the name into:
@TableName and @Schema but @Schema could concievably have nothing in it if passed as 'Roles'.
I was looking at using CHARINDEX looking at '.', but it could return 0 for either finding it in the first position or not there at all (Probably not an issue here since I doubt anyone would have a '.' in the 1st position).
Thanks,
Tom
June 6, 2011 at 3:08 pm
Red flags everywhere :exclamation:
It sounds like you're headed into dynamic-sql-hell :sick:
Are you confident in your design? If not, if you want to share a bit more about what you're trying to do we may be able to steer you in a direction that will result in more maintainable, better performing code.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2011 at 3:22 pm
tshad (6/6/2011)
The reason I am asking is that I am using the following query:SELECT @IsIdentity = sys.columns.is_identity
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.is_identity = 1 AND
sys.objects.type IN ( N'U' )
AND sys.objects.name = 'Roles'
AND SCHEMA_NAME(schema_id) = 'intr'
But the table name will come as a parameter:
@TableName = 'intr.Roles'.
I want to put this into the select statement.
But since the @TableName will come in one of three ways as mentioned above.
So I need to split out the name into:
@TableName and @Schema but @Schema could concievably have nothing in it if passed as 'Roles'.
I was looking at using CHARINDEX looking at '.', but it could return 0 for either finding it in the first position or not there at all (Probably not an issue here since I doubt anyone would have a '.' in the 1st position).
Thanks,
Tom
Now I see the light 😀
It seems to me, then, that Lowell's solution should work perfectly (thanks Lowell, I wasn't aware of PARSENAME()!) for what you need. PARSENAME works well whether your string contains 1, 2, 3, or 4 parts of the name:
DECLARE @string as varchar(50)
SET @string = 'Discount'
SELECT
PARSENAME(@string,1)
DECLARE @string as varchar(50)
SET @string = 'dbo.Discount'
SELECT
PARSENAME(@string,1)
DECLARE @string as varchar(50)
SET @string = 'DB4.dbo.Discount'
SELECT
PARSENAME(@string,1)
...all return part 1 of the identifier:
Discount
_________________________________
seth delconte
http://sqlkeys.com
June 6, 2011 at 3:25 pm
opc.three (6/6/2011)
Red flags everywhere :exclamation:It sounds like you're headed into dynamic-sql-hell :sick:
Are you confident in your design? If not, if you want to share a bit more about what you're trying to do we may be able to steer you in a direction that will result in more maintainable, better performing code.
Nice avatar 🙂
_________________________________
seth delconte
http://sqlkeys.com
June 6, 2011 at 3:29 pm
seth delconte (6/6/2011)
Nice avatar 🙂
Awesome 😎
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2011 at 3:40 pm
The ParseName function was exactly what I was looking for and irritated that I couldn't find it.
I looked all over the net looking for string functions and none of the articles had anything about this function.
Thanks,
Tom
June 6, 2011 at 3:56 pm
I did find a problem when the schema was blank.
If @tablename = 'Roles', this will return nothing. I thought about using a Case statement but not sure how to get that to work here. In essance, I need to have the query ignore the last line if it returns nothing.
SELECT @IsIdentity = sys.columns.is_identity
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.is_identity = 1 AND
sys.objects.type IN ( N'U' )
AND sys.objects.name = PARSENAME(@tableName,1)
AND SCHEMA_NAME(schema_id) = PARSENAME(@tableName,2)
Thanks,
Tom
June 6, 2011 at 4:03 pm
AND (SCHEMA_NAME(schema_id) = PARSENAME(@tableName,2) or PARSENAME(@tableName,2) is null)
June 6, 2011 at 4:25 pm
tshad (6/6/2011)
I did find a problem when the schema was blank.If @tablename = 'Roles', this will return nothing. I thought about using a Case statement but not sure how to get that to work here. In essance, I need to have the query ignore the last line if it returns nothing.
SELECT @IsIdentity = sys.columns.is_identity
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.is_identity = 1 AND
sys.objects.type IN ( N'U' )
AND sys.objects.name = PARSENAME(@tableName,1)
AND SCHEMA_NAME(schema_id) = PARSENAME(@tableName,2)
Thanks,
Tom
This seems to work just dandy (I've modified some of your code for easier testing):
DECLARE @string as varchar(50)
SET @string = 'Roles'
SELECT sys.objects.name,SCHEMA_NAME(schema_id)
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.is_identity = 1
AND sys.objects.type IN ( N'U' )
AND sys.objects.name = PARSENAME(@string,1)
AND SCHEMA_NAME(schema_id) like
CASE WHEN PARSENAME(@string,2) IS NULL THEN '%'
ELSE PARSENAME(@string,2)
END
_________________________________
seth delconte
http://sqlkeys.com
June 6, 2011 at 4:28 pm
Michael Valentine Jones (6/6/2011)
AND (SCHEMA_NAME(schema_id) = PARSENAME(@tableName,2) or PARSENAME(@tableName,2) is null)
I like yours better 🙂
_________________________________
seth delconte
http://sqlkeys.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply