Replacing a text globally

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

and replace all of them with "abc" without affecting the databases an application.

Can u please help me with step by step guidance?

muralidaran rYou want to change the names of things without affecting an application that uses this data?
Slow down and think about what effects this will have.|||hi

thanks for your reply

the only change expected from application is the change of connection string.

Muralidaran r|||Your application doesn't do anything like

SELECT Field1 FROM xyzTable

?|||My application code never uses table name or column name.

it uses only stored procedure names.

muralidaran r|||I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

I rest my case.|||If your application calls stored procedure names have "xyz" in them, then there is no solution to your problem... When you rename the called procedures, the application will fail (because it will still try to use the old names that contain "xyz" instead of "abc").

-PatP|||due to company/product name change,

Can u please help me with step by step guidance?

Sure, learn how to code correctly|||Thank you guys. Now I will retype the question. try to understand better. Actually the coding was done by someone else and now i have to manage this. OK. try to give a solution.

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
content of all stored procedures

and replace all of them with "abc"

Can u please help me with step by step guidance?

muralidaran r|||It shouldn't be done IMO.
Users don't see database name(s), table names, column names or content of stored procedures. Users should only see data that you let them see.|||How to search for a string "xyz" in

database name,

table name,

column name,

content of all stored procedures

muralidaran|||I want to search for a string "xyz" in
...
and replace all of them with "abc"

I did listen and I responded appropriately. My advice is don't do it, simple as that.

As always you can chose to ignore my advice but I can assure you that others will respond in a similar fashion.|||Hi

Already i started changing the string by some manuall method which is tedious and hard.

at each and every stage i am checking the applications performance and accuracy.

I need help to speed up this.|||Speaking from a SQL2000 perspective, you need to check "name" in sysobjects for tablenames, view names, proc names, etc. You need to check text in syscomments for sproc content, etc, you need to check syscolumns for column names, etc.

I'm sure for every place you catch, there will be 2 you don't catch.

I'm with GeorgeV, DO NOT DO IT. Let sleeping dogs lay.

Have fun.|||Dear muralidaran_r,

Have you understood what the users here are telling you?
If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name.

Or are you asking how to search for and change data stored in the tables?|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.

....or, find another career|||"If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name"

Yes to modify all, is there any automated method, tool, procedure available.

Muralidaran r|||No, not that I know of.

Replacing a NULL in a view

I have created a view over a table that extracts production
information. The undelying table contains rows not just for product
code changed but also for speed changes.
The folloing view fillers out the speed changes and seems to work bar
one small problem. The EndTime for the currently running product will
be NULL as it is still running. This is a proble as I miss the current
production information. Is there a way to make the EndTime NULL in the
MAX function if the table value is NULL or could NULL be replaced with
the current Datetime
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
Many thanks
JimYou can use ISNULL to provide a different value for one that is NULL.
for example
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, ISNULL(MAX(EndTime), GETDATE()) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230512.64cddbc6@.posting.google.com...
> I have created a view over a table that extracts production
> information. The undelying table contains rows not just for product
> code changed but also for speed changes.
> The folloing view fillers out the speed changes and seems to work bar
> one small problem. The EndTime for the currently running product will
> be NULL as it is still running. This is a proble as I miss the current
> production information. Is there a way to make the EndTime NULL in the
> MAX function if the table value is NULL or could NULL be replaced with
> the current Datetime
> SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
> HoursRun
> FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime
> FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode) ProdLog
> ORDER BY StartTime, Unit, Line, ProductCode
> Many thanks
> Jim

Rephrase my Issue with Pivot Table

I have two tables (definitions follow) one called Product, the other called
Traveler_Step. The PK-FK link is the Traveler_Step_#. Each product can
only be at one step at a time. If the product is done processing the
Traveler_Step_# is 0. Not all steps have a traveler at them. What I need
is a query that will return all the steps from the Traveler_Step table that
have a value of -1 in the WIPStep field. The query needs to order the steps
by WIP_Status_Order. Also, there are many different Traveler_Step_# numbers
for different travelers that have the same WIPStep for them. Running the
following query against the two tables:
SELECT
Product.Ingot_Number,
Product.Traveler_Number,
MIN(Traveler_Step.Step) AS Step,
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description,
COUNT(Product.Product_#) AS PieceCount
FROM
Product
RIGHT OUTER JOIN
Traveler_Step ON Product.Traveler_Step_# = Traveler_Step.Traveler_Step_#
GROUP BY
Product.Ingot_Number,
Product.Traveler_Number,
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description
HAVING
Traveler_Step.WIPStep < 0
ORDER BY
Product.Traveler_Number,
Traveler_Step.WIP_Status_Order
I get the following results:
NULL NULL 10 -1 1 Weigh/Record 0
NULL NULL 23 -1 3 Hard Pickle 0
NULL NULL 55 -1 4 Hot Roll Intermediate 0
NULL NULL 15 -1 5 UT Test 0
NULL NULL 20 -1 6 Beta Quench 0
NULL NULL 25 -1 7 Grit Blast 0
NULL NULL 30 -1 8 Pickle 0
NULL NULL 40 -1 9 Air Anneal 0
NULL NULL 45 -1 10 Grit Blast 0
NULL NULL 50 -1 11 Pickle 0
NULL NULL 60 -1 12 Cold Roll Intermediate 0
NULL NULL 80 -1 13 Vacuum Anneal Hang 0
NULL NULL 90 -1 14 Cold Roll Final 0
NULL NULL 115 -1 15 Vacuum Anneal F/P 0
NULL NULL 130 -1 16 Plane Width 0
NULL NULL 140 -1 17 Machine Shape 0
NULL NULL 150 -1 18 Machine Grooves 0
NULL NULL 165 -1 19 Shear Final Length 0
U06436L 53086A 15 -1 5 UT Test 5
U06436L 53086B 15 -1 5 UT Test 4
U06450L 53223J 140 -1 17 Machine Shape 2
U06450L 53223L 140 -1 17 Machine Shape 16
U06460L 53236A 140 -1 17 Machine Shape 5
U06460L 53237K 40 -1 9 Air Anneal 4
U06460L 53237M 40 -1 9 Air Anneal 4
U06460L 53237N 40 -1 9 Air Anneal 3
U06494L 53248G 20 -1 2 Grit Blast 1 *
U06494L 53307A 20 -1 6 Beta Quench 4
U06494L 53307B 20 -1 6 Beta Quench 3
I noticed that step 2 does not appear in the first fields with null values.
But one operation is at step 2 (marked with a star (*)). I need to get all
the steps to appear on the left of the matrix, then list all the items in
the corresponding row of the matrix depending on the
Traveler_Step.WIP_Status_Order field. I can get the steps to appear but as
you can see in the query results the right join is not returning all the
traveler_steps with null values. So I would like to see
Traveler 12345 Traveler 67899 Traveler
23343 Traveler 223344
Step 1
Step 2
x
Step 3
Step 4
Step 5 x
x
Step 6 x
Step 7
...
Step 19
Where the traveler numbers are the columns and the X corresponds to the
step in the rows. Any help?
john
CREATE TABLE [dbo].[Product](
[Product_#] [int] IDENTITY(1,1) NOT NULL,
[Traveler_#] [int] NOT NULL,
[Type_#] [int] NOT NULL,
[Part_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Date_Shipped] [datetime] NULL,
[Operator_#] [int] NOT NULL,
[Create_Date] [datetime] NOT NULL,
[Shop_Order_#] [int] NOT NULL,
[Date_Closed] [datetime] NULL,
[Ingot_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status_#] [int] NULL,
[Anneal_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MRT_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Double_Single_Length] [int] NULL,
[Carton_#] [int] NULL,
[Weight] [int] NULL,
[Sister_Piece_#] [int] NULL,
[Slab_Traveler_#] [int] NULL,
[Chemistry_#] [int] NULL,
[Traveler_Number] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Plate_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinalID] [bit] NULL CONSTRAINT [DF_Product_FinalID] DEFAULT (0),
[Traveler_Step_#] [int] NULL,
[Marked] [int] NULL CONSTRAINT [DF_Product_Marked] DEFAULT (0),
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Product_#] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Traveler_Step](
[Traveler_Step_#] [int] IDENTITY(1,1) NOT NULL,
[Traveler_#] [int] NOT NULL,
[Step] [int] NULL,
[Step_Description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Station_#] [int] NOT NULL,
[Sub_Station_#] [int] NULL CONSTRAINT [DF_Traveler_Step_Sub_Station_#]
DEFAULT (0),
[Kanban_Point] [bit] NULL,
[Dependant_Traveler_Step_#] [int] NULL,
[Work_Standard_#] [int] NULL,
[Kanban_Notify_Station_#] [int] NULL,
[WIPStep] [int] NULL,
[Enabled] [bit] NULL,
[WIP_Status_Order] [int] NULL,
CONSTRAINT [PK_Traveler_Step] PRIMARY KEY CLUSTERED
(
[Traveler_Step_#] ASC
) ON [PRIMARY]
) ON [PRIMARY]I didn't see this before answering your next post.
You SQL query should probably be:
SELECT
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description,
Product.Ingot_Number,
Product.Traveler_Number,
MIN(Traveler_Step.Step) AS Step,
COUNT(Product.Product_#) AS PieceCount
FROM
Traveler_Step
LEFT OUTER JOIN Product
ON Product.Traveler_Step_# = Traveler_Step.Traveler_Step_#
GROUP BY
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description,
Product.Ingot_Number,
Product.Traveler_Number
HAVING
Traveler_Step.WIPStep < 0 /* - make sure this is removing
rows you want */
ORDER BY
Traveler_Step.WIP_Status_Order,
Product.Traveler_Number

repeated words retrival

Hi,

I have a column like product name where in i want to retrive repeated words in the same row.

Ex:
Table Name: product_tbl
Column Name: productname
data: xyz and abc and ltd

Here the word and is repeated twice.

please provide query for retrive these type of cases.Question moved to SQL Server Forum from the Articles section.

Repeat Row Values in Matrix Report

Is it possible to repeat row values in a matrix report?
Current:
Product Color Stock
Product A Red 7
Blue 2
Green 1
To:
Product Color Stock
Product A Red 7
Product A Blue 2
Product A Green 1
Thanks.Anyone has any idea?
Thanks,
Luca
"SStoyko" wrote:
> Is it possible to repeat row values in a matrix report?
> Current:
> Product Color Stock
> Product A Red 7
> Blue 2
> Green 1
>
> To:
> Product Color Stock
> Product A Red 7
> Product A Blue 2
> Product A Green 1
>
> Thanks.
>

Repeat details section if

Is there a way to make the details section repeat depending on the quantity that is associated with a record. For example we have a product and if the customer orders more then one we want the sticker to print out twice. I am running crystal reports 8.
Thanks
DaveYou can set the number of copies to print , depending on the quantity|||How do you generate report?
If you generate it on customerwise, thru coding you need to check the number of records it prints and accordingly set print copies