Automation Script
in [Programming SQL]
|
Prev: invalid procedure call or argument destination on line 9
Next: How to tag 500 rows of a table without a unique key
From: Max2006 on 3 Jul 2008 17:06 Hi, I have to go to all tables within a database and assign a default value to the column DATE_CREATED. I am considering creating an automated script to do that. Is there any existing tool for these kinds of global changes? Thank you, Max
From: TheSQLGuru on 3 Jul 2008 17:18 do some dynamic script generation by joining the relevant system tables. Here is an example of this that I just now did for a work requirement: select 'select ''' + s.name + '.' + o.name + ''' as ''tblname'', count(*) from [' + s.name + '].[' + o.name + '] go' from sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id where type = 'u' order by s.name, o.name grab the output and paste it and execute: select 'audit.BookedLoanApplications' as 'tblname', count(*) from [audit].[BookedLoanApplications] go select 'audit.DealerApplications' as 'tblname', count(*) from [audit].[DealerApplications] go .... -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Max2006" <alanalan1(a)newsgroup.nospam> wrote in message news:A421B773-DF24-4D1C-BAE2-BB199C6E43C7(a)microsoft.com... > Hi, > > I have to go to all tables within a database and assign a default value to > the column DATE_CREATED. I am considering creating an automated script to > do that. Is there any existing tool for these kinds of global changes? > > Thank you, > Max > >
From: Max2006 on 4 Jul 2008 08:57 Thanks Kevin for help. Is there any existing tool to so such tasks? Max "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:LdWdnUAyaeVk3PDVnZ2dnUVZ_rrinZ2d(a)earthlink.com... > do some dynamic script generation by joining the relevant system tables. > Here is an example of this that I just now did for a work requirement: > > select 'select ''' + s.name + '.' + o.name + ''' as ''tblname'', count(*) > from [' + s.name + '].[' + o.name + '] > > go' > > from sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id > > where type = 'u' > > order by s.name, o.name > > > > grab the output and paste it and execute: > > > > select 'audit.BookedLoanApplications' as 'tblname', count(*) from > [audit].[BookedLoanApplications] > > go > > select 'audit.DealerApplications' as 'tblname', count(*) from > [audit].[DealerApplications] > > go > > ... > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "Max2006" <alanalan1(a)newsgroup.nospam> wrote in message > news:A421B773-DF24-4D1C-BAE2-BB199C6E43C7(a)microsoft.com... >> Hi, >> >> I have to go to all tables within a database and assign a default value >> to the column DATE_CREATED. I am considering creating an automated script >> to do that. Is there any existing tool for these kinds of global changes? >> >> Thank you, >> Max >> >> > >
From: TheSQLGuru on 5 Jul 2008 11:03
I think there may be tools that will allow you to make a change to a table and apply it to other tables somehow. but such things are so variant you best and most appropriate option (imho) is to simply script them yourselves using the mechanism I proposed. I use this form of build-it scripting very frequently to do all sorts of things. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Max2006" <alanalan1(a)newsgroup.nospam> wrote in message news:1F3A5A35-D99C-4A14-98B6-7E42C13F640A(a)microsoft.com... > > Thanks Kevin for help. > Is there any existing tool to so such tasks? > > Max > > "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message > news:LdWdnUAyaeVk3PDVnZ2dnUVZ_rrinZ2d(a)earthlink.com... >> do some dynamic script generation by joining the relevant system tables. >> Here is an example of this that I just now did for a work requirement: >> >> select 'select ''' + s.name + '.' + o.name + ''' as ''tblname'', count(*) >> from [' + s.name + '].[' + o.name + '] >> >> go' >> >> from sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id >> >> where type = 'u' >> >> order by s.name, o.name >> >> >> >> grab the output and paste it and execute: >> >> >> >> select 'audit.BookedLoanApplications' as 'tblname', count(*) from >> [audit].[BookedLoanApplications] >> >> go >> >> select 'audit.DealerApplications' as 'tblname', count(*) from >> [audit].[DealerApplications] >> >> go >> >> ... >> >> -- >> Kevin G. Boles >> Indicium Resources, Inc. >> SQL Server MVP >> kgboles a earthlink dt net >> >> >> "Max2006" <alanalan1(a)newsgroup.nospam> wrote in message >> news:A421B773-DF24-4D1C-BAE2-BB199C6E43C7(a)microsoft.com... >>> Hi, >>> >>> I have to go to all tables within a database and assign a default value >>> to the column DATE_CREATED. I am considering creating an automated >>> script to do that. Is there any existing tool for these kinds of global >>> changes? >>> >>> Thank you, >>> Max >>> >>> >> >> |