March 3, 2008 at 5:36 pm
Is there a function which will return 1 if a string is a valid sql table name and 0 if it is not, or something similar?
I am trying to ensure that this procedure will reject input which is not a valid name.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
March 3, 2008 at 5:42 pm
Do you mean to check if the string can become a table name or to check that a table with name = string exists?
March 3, 2008 at 5:48 pm
There is no built in function but you could make one. You should use the infomation_schema view to validate.
if exists(
select 1
from information_schema.tables
where table_name = 'TableName')
March 3, 2008 at 5:56 pm
ksullivan (3/3/2008)
Do you mean to check if the string can become a table name or to check that a table with name = string exists?
I am trying to check if it could become one.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
March 3, 2008 at 7:28 pm
timothyawiseman (3/3/2008)
ksullivan (3/3/2008)
Do you mean to check if the string can become a table name or to check that a table with name = string exists?I am trying to check if it could become one.
Almost anything and everything can be a table name if it's included in square brackets... are you asking for a function that makes sure a table name doesn't need square bracket's? You could come close with the very simple amount of Regex available in the LIKE statment... but, you'd also need a list of reserved words, etc. And, I don't believe there's a function that we can get to in SQL Server that would to the trick for us...
--Jeff Moden
March 3, 2008 at 8:07 pm
I am trying to ensure that this procedure will reject input which is not a valid name.
I took this as a function to check if a table already exists. Like Jeff said a table can be named just about anything. The only real constraiint is if it already exists.
March 3, 2008 at 8:13 pm
...and has no more than 128 characters.
March 4, 2008 at 12:54 am
Thanks Jeff. I was hoping for something that would identify a valid identifier that does not require brackets (or double quotes when quoted identifier is on), but like you said I came pretty close with some like statements. Its not absolutely perfectly, but it should catch the bulk of cases that the users are like to come up with.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
March 4, 2008 at 5:49 am
As everyone else has already pointed out, if you pass in brackets, you can make a table named almost anything, including [].
But, if we assume you either strip the brackets out or prevent them from being passed in, you could do something like this:
DECLARE @tablename NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)
--SET @tablename = 'procedure'
SET @tablename = 'x'
BEGIN TRY
BEGIN TRANSACTION
SET @sql = 'create table ' + @tablename + ' (x int)'
EXEC sp_executesql @sql
ROLLBACK TRANSACTION
SELECT 0 ;
END TRY
BEGIN CATCH
SELECT 1 ;
ROLLBACK TRANSACTION
END CATCH
I mainly did this an exercise, so it would be possible to clean it up a bit & make it into a functional procedure. But, because of the possibility of the use of brackets, this is largely meaningless. Still, fun practice.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply