December 13, 2012 at 2:37 pm
Hello fellow developers,
I have been trying to look for a column in a table A for joining it with other table B . I'm not sure which column to use in table A as it contains numerous columns.
Only way would be to try to find a word in table A,which is present in table B.
Any suggestions ?
Thanks
SM
December 13, 2012 at 2:55 pm
SQL SERVER ROOKIE (12/13/2012)
Hello fellow developers,I have been trying to look for a column in a table A for joining it with other table B . I'm not sure which column to use in table A as it contains numerous columns.
Only way would be to try to find a word in table A,which is present in table B.
Any suggestions ?
Thanks
SM
Are there any foreign keys between these tables?
_______________________________________________________________
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/
December 13, 2012 at 2:56 pm
--BSC is one of the largest and most exciting centers for higher education in the commonwealth. Here in our idyllic setting, you'll find an institution steeped in history, ready for the future, and rooted in an unwavering commitment to excellence
EXEC SearchAllTables 'BSC is one of the largest and most exciting centers for higher education in the commonwealth. Here in our idyllic setting, you''ll find an institution steeped in history, ready for the future, and rooted in an unwavering commitment to excellence'
GO
--DROP PROCEDURE SearchAllTables
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
[font="Courier New"]ZenDada[/font]
December 13, 2012 at 3:00 pm
Hi Sean,
I have been told that table A and B are related (2 columns from table A have to be concatenated to form a key ..but I'm not sure which column to use in tbale B). There is no documention exsplaning the relationship between the tables.So only way left for me is to manually look for data in Table B ;(.
Thanks
Swarup
December 13, 2012 at 3:01 pm
There are a few other versions similar to the script above that have been posted around this site repeatedly. The code posted looks like it should work but the poster failed to mention the most important thing about a script like this. DO NOT RUN THIS IN PRODUCTION!!! It will be slower than frozen molasses in the arctic tundra. There simply is no way to look in every column of every row in every table and have it be anything other than slow. Make a backup of your database, put it on a dev server and then run this.
_______________________________________________________________
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/
December 13, 2012 at 3:02 pm
SQL SERVER ROOKIE (12/13/2012)
Hi Sean,I have been told that table A and B are related (2 columns from table A have to be concatenated to form a key ..but I'm not sure which column to use in tbale B). There is no documention exsplaning the relationship between the tables.So only way left for me is to manually look for data in Table B ;(.
Thanks
Swarup
There are no foreign keys? That sounds like a painful "design" you have been handed.
_______________________________________________________________
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/
December 13, 2012 at 4:15 pm
Look at the data, preferably from the application, and find some data that comes from both tables. Then find those rows in sSMS and see if you can derive a key.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 13, 2012 at 7:25 pm
SQL SERVER ROOKIE (12/13/2012)
Hi Sean,I have been told that table A and B are related (2 columns from table A have to be concatenated to form a key ..but I'm not sure which column to use in tbale B). There is no documention exsplaning the relationship between the tables.So only way left for me is to manually look for data in Table B ;(.
Thanks
Swarup
If the people that told you that know that much, why can't they also tell you what the columns are?
--Jeff Moden
December 14, 2012 at 7:05 am
Thank you all of you!!!! Manually looking for similar data seems to be the best solution for my prob!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply