Add date in sql server script name?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I want to create views,indexes and stroed procedure name with date.

Ex:-At present i can create index (Create Index MY_INDEX ON dbo.TableName) so index name will be
"MY_INDEX"
But i want it as "MY_INDEX_20131119"?
So i want something which can do it like

CREATE INDEX MY_INDEX+GETDATE ON dbo.TableName
( )

Finally i want to create my script name with concatenation of execution date.

Thanks

Posted 18-Nov-13 19:36pm

StackQ

Updated 18-Nov-13 19:37pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2013-11-18T19:49:00

Solution 1

You have to use dynamic scripts - as SQL does not allow variable in CREATE TABLE...

A T-SQL sample...

SQL

EXECUTE('CREATE TABLE MYINDEX_' + REPLACE(CAST(GETDATE() AS NVARCHAR), ' ', '')+ ' (ID INT NOT NULL, NAME NVARCHAR(120))')

Posted 18-Nov-13 19:49pm

Kornfeld Eliyahu Peter

Updated 18-Nov-13 23:13pm

v2

Comments

StackQ 19-Nov-13 2:36am

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CREATE TABLE MYINDEX_'.

Kornfeld Eliyahu Peter 19-Nov-13 3:00am

CAST(GETDATE() AS NVARCHAR) may give you a time with : inside it...
Try to format the date part in some other way (: is forbidden in table names)...

StackQ 19-Nov-13 3:45am

1st thing it's always giving error at 'CREATE TABLE MYINDEX_', after solving it i can go ahead.

Kornfeld Eliyahu Peter 19-Nov-13 3:47am

Replace EXEC with PRINT and copy here the result please...

StackQ 19-Nov-13 4:05am

O/P is:- CREATE TABLE MYINDEX_Nov1920132:33PM (ID INT NOT NULL, NAME NVARCHAR(120))

Kornfeld Eliyahu Peter 19-Nov-13 4:14am

The problem is the ':' i the generated table name (as I said before).
You have to remove/replace it or generate the table name in some different way...
1. REPLACE(REPLACE(CAST(GETDATE() AS NVARCHAR), ' ', ''), ':', '')
2. REPLACE(CONVERT(GETDATE(), GETDATE(), 105), '-', '')
3. Anything else that fits your project and valid as table name...

StackQ 19-Nov-13 5:06am

1:-PRINT 'CREATE TABLE MYINDEX_' + RIGHT(CONVERT(VARCHAR(8), GETDATE(), 112),2)+LEFT(CONVERT(VARCHAR(8), GETDATE(), 112),6)+ ' (ID INT NOT NULL, NAME NVARCHAR(120))';
O/P is:-
CREATE TABLE MYINDEX_19201311 (ID INT NOT NULL, NAME NVARCHAR(120))

2:-EXEC 'CREATE TABLE MYINDEX_' + RIGHT(CONVERT(VARCHAR(8), GETDATE(), 112),2)+LEFT(CONVERT(VARCHAR(8), GETDATE(), 112),6)+ ' (ID INT NOT NULL, NAME NVARCHAR(120))'
/P:-
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CREATE TABLE MYINDEX_'.

#USING SQL SERVER 2008
For EXEC Just showing incorrect syntax at CREATE, even i have tried

1:-print 'CREATE TABLE MYINDEX' + ' (ID INT NOT NULL, NAME NVARCHAR(120))'
O/P:-CREATE TABLE MYINDEX (ID INT NOT NULL, NAME NVARCHAR(120))
2:-EXEC 'CREATE TABLE MYINDEX' + ' (ID INT NOT NULL, NAME NVARCHAR(120))'
O/P:-'CREATE TABLE MYINDEX' + ' (ID INT NOT NULL, NAME NVARCHAR(120))'

So problem not with table name with syntax or wrong way.Suggest some other way...
Thanks

Kornfeld Eliyahu Peter 19-Nov-13 5:12am

You just right. No table name problems here. My head is the problem...
Do EXECUTE('dynamic sql')...
(EXEC is to run SPs)

StackQ 19-Nov-13 5:48am

thank u so much, this time i have done it with your help, once again so many thanks :)
*****

Kornfeld Eliyahu Peter 19-Nov-13 5:53am

I'm glad to help you...