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: 1000539 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)
Intro to Tally Tables and Functions

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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software