Cambio ID en todas las tablas de una bbdd

Algunas veces es necesario cambiar un ID en todas las tablas de una bbdd, con este script lo podemos lograr

 

IF OBJECT_ID ( 'usp_cambioid', 'P' ) IS NOT NULL

DROP PROCEDURE usp_cambioid;

GO

 

create proc usp_cambioid @idviejo decimal, @idnuevo decimal

as

begin

IF @idviejo IS NULL or @idnuevo is null

BEGIN

PRINT 'ERROR: falta un parametro. use usp_cambioid idviejo, idnuevo'

RETURN

END

begin tran

DECLARE @Tabla varchar(100), @Campo varchar(100), @sql varchar(500), @sqlacu varchar(4000)

declare @count int

set @count = 0

set @sql = ''

set @sqlacu = 'set nocount on' +char(13)+'begin tran '

declare ctablas cursor for

SELECT sysObjects.name as 'Tabla', sysColumns.name as 'Campo'

FROM sysObjects, sysColumns

WHERE sysObjects.id = sysColumns.id and sysObjects.xtype = 'u'

and sysColumns.name = ('id') order by sysObjects.name

open ctablas

FETCH NEXT FROM ctablas into @Tabla, @Campo;

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql = 'update ' + @tabla + ' set ' + @campo + ' = ' +

cast(@idnuevo as varchar(100)) + ' where ' + @campo + ' = ' +

cast(@idviejo as varchar(100))

set @sqlacu = @sqlacu +char(13)+ @sql

FETCH NEXT FROM ctablas into @Tabla, @Campo;

END;

set @sqlacu = @sqlacu + char(13) + 'IF (@@ERROR <> 0) BEGIN ' + char(13) +

' print ''error'''+ char(13) + ' rollback tran ' + char(13) + 'end' + char(13)+

'commit tran'

execute (@sqlacu)

--print @sqlacu

CLOSE ctablas;

DEALLOCATE ctablas;

end

commit tran

Excelente articulo sobre shells scripts en linux

Linux Shell Scripting Tutorial v1.05r3 A Beginner's handbook Copyright © 1999-2002 by Vivek G. Gite <vivek@nixcraft.com> origen http://freeos.com/guides/lsst/index.html (Formally know as vivek-tech.com)

---

Table of Contents

Chapter 1: Quick Introduction to Linux

What Linux is?
Who developed the Linux?
How to get Linux?
How to Install Linux
Where I can use Linux?
What Kernel Is?
What is Linux Shell?
How to use Shell
What is Shell Script ?
Why to Write Shell Script ?
More on Shell...

Chapter 2: Getting started with Shell Programming

How to write shell script
Variables in shell
How to define User defined variables (UDV)
Rules for Naming variable name (Both UDV and System Variable)
How to print or access value of UDV (User defined variables)
echo Command
Shell Arithmetic
More about Quotes
Exit Status
The read Statement
Wild cards (Filename Shorthand or meta Characters)
More commands on one command line
Command Line Processing
Why Command Line arguments required
Redirection of Standard output/input i.e. Input - Output redirection
Pipes
Filter
What is Processes
Why Process required
Linux Command(s) Related with Process

Chapter 3: Shells (bash) structured Language Constructs

Decision making in shell script ( i.e. if command)
test command or [ expr ]
if...else...fi
Nested ifs
Multilevel if-then-else
Loops in Shell Scripts
for loop
Nested for loop
while loop
The case Statement
How to de-bug the shell script?

Chapter 4: Advanced Shell Scripting Commands

/dev/null - to send unwanted output of program
Local and Global Shell variable (export command)
Conditional execution i.e. && and ||
I/O Redirection and file descriptors
Functions
User Interface and dialog utility-Part I
User Interface and dialog utility-Part II
Message Box (msgbox) using dialog utility
Confirmation Box (yesno box) using dialog utility
Input (inputbox) using dialog utility
User Interface using dialog Utility - Putting it all together
trap command
The shift Command
getopts command

Chapter 5: Essential Utilities for Power User

Preparing for Quick Tour of essential utilities
Selecting portion of a file using cut utility
Putting lines together using paste utility
The join utility
Translating range of characters using tr utility
Data manipulation using awk utility
sed utility - Editing file without using editor
Removing duplicate lines from text database file using uniq utility
Finding matching pattern using grep utility

Chapter 6: Learning expressions with ex

Getting started with ex
Printing text on-screen
Deleting lines
Coping lines
Searching the words
Find and Replace (Substituting regular expression)
Replacing word with confirmation from user
Finding words
Using range of characters in regular expressions
Using & as Special replacement character
Converting lowercase character to uppercase

Chapter 7: awk Revisited

Getting Starting with awk
Predefined variables of awk
Doing arithmetic with awk
User Defined variables in awk
Use of printf statement
Use of Format Specification Code
if condition in awk
Loops in awk
Real life examples in awk
awk miscellaneous
sed - Quick Introduction
Redirecting the output of sed command
How to write sed scripts?
More examples of sed

Chapter 8: Examples of Shell Scripts

Logic Development:
Shell script to print given numbers sum of all digit
Shell script to print contains of file from given line number to next given number of lines
Shell script to say Good morning/Afternoon/Evening as you log in to system
Shell script to find whether entered year is Leap or not
Sort the given five number in ascending order (use of array)
Command line (args) handling:
Adding 2 nos. suppiled as command line args
Calculating average of given numbers on command line args
Finding out biggest number from given three nos suppiled as command line args
Shell script to implement getopts statement.
Basic math Calculator (case statement)
Loops using while & for loop:
Print nos. as 5,4,3,2,1 using while loop
Printing the patterns using for loop.
Arithmetic in shell scripting:
Performing real number calculation in shell script
Converting decimal number to hexadecimal number
Calculating factorial of given number
File handling:
Shell script to determine whether given file exist or not.
Screen handling/echo command with escape sequence code:
Shell script to print "Hello World" message, in Bold, Blink effect, and in different colors like red, brown etc.
Background process implementation:
Digital clock using shell script
User interface and Functions in shell script:
Shell script to implements menu based system.
System Administration:
Getting more information about your working environment through shell script
Shell script to gathered useful system information such as CPU, disks, Ram and your environment etc.
Shell script to add DNS Entery to BIND Database with default Nameservers, Mail Servers (MX) and host
Integrating awk script with shell script:
Script to convert file names from UPPERCASE to lowercase file names or vice versa.

Chapter 9: Other Resources

Appendix - A : Linux File Server Tutorial (LFST) version b0.1 Rev. 2
Appendix - B : Linux Command Reference (LCR)
About the author
About this Document