Valid Sql table name

Question

Post reply

Valid Sql table name

TimothyAWiseman

SSCrazy Eights

Points: 8819
More actions
March 3, 2008 at 5:36 pm

#187273

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/

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

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

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Do you mean to check if the string can become a table name or to check that a table with name = string exists?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

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')

My blog: http://jahaines.blogspot.com

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

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/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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

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)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

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.

My blog: http://jahaines.blogspot.com

ksullivan SSCrazy Points: 2346 More actions · Answer 6

ksullivan

SSCrazy

Points: 2346

March 3, 2008 at 8:13 pm

#784754

...and has no more than 128 characters.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 7

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/

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 8

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning