how to set some restriction on table names while creating?

Question

Post reply

how to set some restriction on table names while creating?

vignesh.ms

Hall of Fame

Points: 3456
More actions
November 26, 2013 at 12:55 am

#300876

Hi There,
I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
could any1 help me regards this?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

You can use DDL triggers for example:

DDL Triggers

or you can use policy based management:

Administer Servers by Using Policy-Based Management

PBM has its disadvantages though. If you create a rule for your table naming convention, already existing tables can fail that rule.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

Can I have query for creating triggers to achieve this ???

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

vignesh.ms (11/26/2013)
Can I have query for creating triggers to achieve this ???

I don't have a query like that (I'm not a DBA), I just know it is possible.

So you'll have to Google like the rest of us...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 4

vignesh.ms

Hall of Fame

Points: 3456

November 26, 2013 at 5:53 am

#1669104

oh okay...

thanks for the reply

Lowell SSC Guru Points: 323504 More actions · Answer 5

vignesh.ms (11/26/2013)
Hi There,
I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
could any1 help me regards this?
Thanks

well, once you establish the "rules" via tSQL, you can then addthem to check the name in a trigger;

here's a fast example of some potential rules:

Declare @TableName varchar(128) ='TeamName_EmpId_tablename'

SELECT SUBSTRING(@TableName,0,CHARINDEX('_',@TableName)),

REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_',''),

SUBSTRING(REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_',''),0,CHARINDEX('_',REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_','')))

DECLARE @error VARCHAR(4000)

--first rule: three part convention: must have two underscores?

if LEN(@TableName) - LEN(REPLACE(@TableName,'_','')) <> 2

@error = 'Naming Convention Failure: All tableNames must follow TeamName_EmpId_tablename format: Issue: missing underscored format.'

--first part must come from a table listing all teams

if not Exists(SELECT 1 FROM AllTeamNames where TeamName = SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))

@error = 'Naming Convention Failure: TeamName portion of naming convention must exist in the table AllTeamNames.'

--second part must com from a table listing all employeeids

if not Exists(SELECT 1 FROM AllEmployeeIDs where EmpId = SUBSTRING(REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_',''),0,CHARINDEX('_',REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_','')))

@error = 'Naming Convention Failure: EmpId portion of naming convention must exist in the table AllEmployeeIDs.'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!