Category: Technology

I work in technology everyday, and occasionally I like to share what I’ve found.

Search and Replace across all tables

Search and replace all occurances of a string in all tables in a database.
From http://vyaskn.tripod.com/

--To replace all occurences of 'America' with 'USA':
EXEC SearchAndReplace 'America', 'USA'
GO

REATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

 

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

Search all tables in a database

Search all text fields across all tables in a database. Taken from http://vyaskn.tripod.com/ –To search all columns of all tables in Pubs database for the keyword “Computer” EXEC SearchAllTables ‘Computer’ GO


CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) )
AS BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

How to change the listening port for Remote Desktop

Often you’ll want to run Remote Desktop services on a non-standard port.  Here’s the steps for changing the listening port on the “host” system.  Taken from the MS Knowledge Base.

View products that this article applies to. This article was previously published under Q306759 Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base: 256986 Description of the Microsoft Windows Registry INTRODUCTION This article describes how to change the port that Remote Desktop listens on. Note The Remote Desktop Connection Client for the Mac supports only port 3389. 3389 is the default port. MORE INFORMATION You can use the Remote Desktop feature in Microsoft Windows XP Professional to connect to your computer from another remote computer. Warning The Remote Assistance feature in Microsoft Windows XP may not work correctly if you change the listening port. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: 307711 Remote Assistance invitation file does not contain correct port number To change the port that Remote Desktop listens on, follow these steps. Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

  1. Start Registry Editor.
  2. Locate and then click the following registry subkey:

 

HKEY_LOCAL_MACHINESystemCurrentControlSetControlTerminalServerWinStationsRDP-TcpPortNumber

  1. On the Edit menu, click Modify, and then click Decimal.
  2. Type the new port number, and then click OK.
  3. Quit Registry Editor.

 

Note When you try to connect to this computer by using the Remote Desktop connection, you must type the new port.304304 How to configure the Remote Desktop client to connect to a specific port when you use Windows XP

 

Order characters numerically

It may be simple, but this SQL will order items numerically (1,2,3,10,20) instead of alpha (1,10,2,20,3).


SELECT ItemID, ItemName, ItemNum, CASE WHEN Len(ItemNum) = 0 THEN 'Z' ELSE SPACE(10- Len(ItemNum)) + ItemNum END OrderCol
FROM table
ORDER BY OrderCol

Using Alt and Title attributes on img tags

I was sent a tip about using alt and title attributes. I’ve noticed for a while now that Netscape 6+ doesn’t show the alt tags when you hover over them. Looking at the W3C specifications, the alt attribute is supposed to be used when the image can’t be displayed, because the image wasn’t found or the browser doesn’t support images. The title attribute is supposed to be used to provide extra information about ANY element and doesn’t have to be used only on img tags. Netscape uses the title attribute to show the little pop-up text when you hover over an image and shows the contents of the alt attribute when the image can’t be loaded. So, what is the correct attribute to use on images? It turns out the best thing to do is use both if you want to have a pop-up show when the user hovers over the image. IE will show the title attribute if it exists and the alt if title isn’t there. The title then could be a much longer description of the image and the alt tag a short explanation of what it’s for so that browser that can’t see the image will know what was supposed to be there.

For example:
<img src=”navicons/registration.jpg” alt=”Registration” title=”Click here to register for the conference”>

Will show “Click here to register for the conference” when you hover over it in either browser. It will show “Registration” in either browser if the image can’t be loaded although IE also shows a broken image icon. For screen readers, they’ll read both, “Registration” as the meaning of the image and the longer description if the user wants more information.

So, long story short, ALWAYS use alt, even if it’s blank(for bullet icons and such), and IF you want a pop-up to show use the title attribute. That said I’m certainly not suggesting that we go back and change pages we’ve already done, just keep it in mind for the future.