Database names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database names

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-26 : 07:40:45

Jason writes "We currently have a management application that allows users to create a database, using a user specified database name.

I need to determine whether the name they have entered is a valid name (ie. it meets the criteria as a valid SQL Server identifier name, as per the Online books documentation.[See Create Database syntax]).

Is there a function within SQL Server to determine this? I have searched high and low, and there doesn't seem to be any references to this sort of check that I can find."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-26 : 07:53:10

list of database attached on the server

Select * From Master..Sysdatabases

Chirag

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-26 : 10:35:28

quote:
"We currently have a management application that allows users to create a database, using a user specified database name."

I am SO SORRY to hear that.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-26 : 11:06:33

That sounds really a bad design. Why do you want create database for each user?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 11:19:44

Sounds like a web-hotel solution.

Peter Larsson
Helsingborg, Sweden

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-09-26 : 11:31:18

"We currently have a management application that allows users to create a database, using a user specified database name."

Gotta ask - WHY???

Live to Throw
Throw to Live

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-26 : 11:44:59

I don't think there are any restrictions..just wrap the crete statement with []

CREATE DATABASE [Why would any #1 ever think to do this database!!!]

Which actually works

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-26 : 11:53:07

who cares why Jason wants to do this. There are many scenarios where a self-service environment like this would be useful.

Anyway, to answer the question - There is no built-in function for you to use that will do this. However, you can certainly build a function that will parse the proposed name the end-user has submitted and reject ones that don't meet your criteria.

If the name passes this check, then you can simply pass the name to the proc you are using that creates your new databases.

-ec

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-26 : 12:09:09

You would just have to handle the error

DECLARE @error int
CREATE DATABASE [Why would any #1 ever think to do this database!!!!#$%^*&(*()_]
SET @error = @@ERROR
IF @error <> 0
PRINT 'Handle Error'
ELSE
PRINT 'No Error'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-09-26 : 13:41:44

Why don't you invent your own standard? A name starting with Alpha and containing only AlphaNumeric and "_" is safe for any sort of name, provided it is not a reserve word.

I think this would be preferable to allowing "anything" because a name containing weird characters, although "valid", might cause something that is not expecting the weird characters to fail. Likewise ridiculously long names are OK, but make life difficult trying to choose a database in the drop down list in QA and so on.

I would go for 30 characters max, [A-Z0-9_] and forced upper case.


IF @strProposedName LIKE '%[^A-Z0-9_]%'
BEGIN
    SELECT [ERROR] = 'Database name is invalid'
END

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources