September 5, 2016 at 3:22 pm
I have 5 columns that I want to add to multiple tables (without using dynamic SQL).
It seems to me there was a way to do things to multiple tables but I can't remember how it was done.
For example:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
Run this as 5 columns (InsertID, InsertDate, LastUpdateID, LastUpdateDate and ConcurrencyID) by running a stored procedure for one table or multiple tables.
Thanks,
Tom
September 5, 2016 at 10:55 pm
Rethink your approach.
It's better to have a separate "ChangeAudit" table where you record all the changes done to the tables.
Normally 1 table to store changes to all audited tables is enough.
With "Last Updated" you're gonna the latest update will wipe out all the history of previous changes.
_____________
Code for TallyGenerator
September 6, 2016 at 3:03 am
I second the recommendation for an audit table.
That said, there's no stored procedure that I know of to run alter table syntax on multiple tables, you have to run it one by one, a table at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2016 at 3:36 am
I too agree that the approach should be reconsidered if you have the luxury.
As Gail mentioned, there is no way than to execute alter scripts for each table.
However you can do some smart work generating the scripts by something like
Select 'Alter table ['+schema_name(schema_id)+'].['+name+'] ADD columnname datatype' from Sys.tables
You should then review the script, and apply in dev and test it thouroughly.
September 6, 2016 at 10:44 am
You can add all 5 columns to a given table in one statement, but you need separate statements for each table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 6, 2016 at 11:08 am
Not the best way to approach auditing if that's what this is for.
To do such on multiple tables, you'd need dynamic sql, multiple alter statements or else use one of the cursor creator built in functions (sp_MSforeachtable, sp_MSforechdb) which is still essentially dynamic SQL underneath. And there are issues with skipped or missed objects with those stored procedures so it's not necessarily a good approach.
What have you written so far?
Sue
September 7, 2016 at 2:16 pm
I partially agree with everyone on the auditing.
I actually do both. I want to be able to know who inserted the record as well as who last updated it (and when).
I would also audit certain tables and not others (but I would still like to know who inserted and last updated the records).
I have these columns on all my tables. It also helps in debugging. I can quickly find out when the record was last touched without having to go to an audit table to find this out.
Thanks,
Tom
September 7, 2016 at 4:12 pm
tshad (9/7/2016)
I have these columns on all my tables. It also helps in debugging. I can quickly find out when the record was last touched without having to go to an audit table to find this out.
Opening an audit table takes about as much time as opening the application data table.
And for debugging - don't you want to know about repeating updates of the same record happening because of a buggy flow in the application code?
Last Updated won't show it, but audit table would.
_____________
Code for TallyGenerator
September 8, 2016 at 7:25 am
I would also caution you here. It seems you are planning on using the UserID as the "whodunnit" value. This is ok assuming that value is unchangeable. And even more important is to not create foreign keys for these columns or you will be stuck never being allowed to delete users.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 8, 2016 at 10:37 am
Actually, not the case on checking audit table vs actual table. If I want to check last person checked on a table and I need to use the audit table as well I need to do two selects or a join.
As far as debugging, I am talking about looking at my data in the query analyser. I already have the records open and I can see the data. If I also need to go to an audit table just to find out the last person that touched it, I have to do a select on the audit table as well. If I am looking at different records in a set in the analyser and I have to keep querying the audit table - it is a little inconvenient.
Also, as I also mentioned, I don't need audits on all my tables. I wouldn't have one on any of my reference tables, for instance.
September 8, 2016 at 10:38 am
I would never use these as foreign keys. I agree that would be a pain.
September 8, 2016 at 10:43 am
tshad (9/8/2016)
Actually, not the case on checking audit table vs actual table. If I want to check last person checked on a table and I need to use the audit table as well I need to do two selects or a join.As far as debugging, I am talking about looking at my data in the query analyser. I already have the records open and I can see the data. If I also need to go to an audit table just to find out the last person that touched it, I have to do a select on the audit table as well. If I am looking at different records in a set in the analyser and I have to keep querying the audit table - it is a little inconvenient.
Also, as I also mentioned, I don't need audits on all my tables. I wouldn't have one on any of my reference tables, for instance.
The challenge becomes what happens if you want to see not who last updated it but who updated it two times ago? If you have a single column, you can't. If you have an audit table you can. I am not saying one is always better than the other but you do need to consider that. Many times a combination of the two is pretty handy. Keeping a column in the table for "whodunnit" last and an audit table to see "whodunnit" each and every time.
And FWIW query analyzer went the way of the dodo bird with sql server 2005. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2016 at 1:46 pm
Which is why, as I mentioned, I use both. I use audit tables on the tables I need to audit but I also have 5 columns on all my tables.
InsertName
InsertDate
LastUpdatedName
LastUpdatedDate
ConcurrencyValue
September 12, 2016 at 1:04 pm
tshad (9/10/2016)
Which is why, as I mentioned, I use both. I use audit tables on the tables I need to audit but I also have 5 columns on all my tables.InsertName
InsertDate
LastUpdatedName
LastUpdatedDate
ConcurrencyValue
Be aware that those extra bytes can drive your DBA (such as me :-D) crazy. I have some rows where this "tag" data exceeds the actual row size by several bytes!
At the very least, you should encode the Names to an integer value. A trigger would look up / assign the appropriate number for each name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 12, 2016 at 3:14 pm
True.
That could be an issue if you have rows that large. I have a customer where some of out tables column definitions exceed the row size by hundreds of bytes. But the actual data would never come close that size as many of the columns are would never be used if other columns are used.
So in that case these 5 don't affect it at all.
But except for this customer the other customers I have dealt with don't have any rows close to that limit.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply