"You could use dynamic SQL to make the CREATE TABLE commands dynamically include the schema of the user running the code."
Yes, that is what I am trying to do. So, your comment gave me a clue as to where to go next with a google search and it turns out that I can assign a variable to the current user as:
set @user = user_name()
I can put this in dynamic sql and place the table location appropriately..
So, that worked, but now wanted to add an additional piece to the dynamic sql. I wanted to place a statement checking to see if the table is there (or 'not null' in the cases of the function I'm using), but my syntax appears incorrect. I am trying to place all of the code in the single exec command (i.e. exec (sql_cmd) - where sql_cmd hold the syntax)..
This is what I have:
'IF OBJECT_ID(''[' + @user + '].' + @curtblname + ''')' + ' IS NOT NULL DROP TABLE [' + @user + '].' + @curtblname +
'SELECT * INTO [' + @user + '].BK_' + @curtblname + '_' + @date + 'TASK' + @task_id +
' FROM ' + @curtblname
So written out non dynamically:
IF OBJECT_ID ('[schema\mark2].project') is not null drop table [schema\mark2].project
select into [schema\mark2].BK_Project_20171101_1000
I think I might be going wrong where I have to put the quotes into the table name in the OBJECT_ID arguments, I have 3 quotes in the dynamic part to take care of the schema and table name part of the above non dynamic part (i.e: OBJECT_ID ('[schema\mark2].project') ..
Can you see anything obvious I am missing syntax wise. Or perhaps do i have to do the OBJECT_ID check as a separate command exec from the part where the table is built?
Thanks for any continued help..!