September 13, 2007 at 11:18 am
Is there way to do a field name search in a particular D.B. for example plug in a field name and SQL Server 2000 returns all tables which have this specified field name?
September 13, 2007 at 11:23 am
Look at the syscolumns table in the given DB. The ID field matches the ID in Sysobjects that this column comes from
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 13, 2007 at 2:04 pm
Have a look as INFORMATION_SCHEMA.COLUMNS
* Noel
September 13, 2007 at 2:34 pm
i use this to find tables or columns that contain some string all the time;
usage: sp_find Customer
CREATE procedure sp_find
@findcolumn varchar(50)
as
begin
set nocount on
select sysobjects.name as TableFound,syscolumns.name as ColumnFound
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
and (syscolumns.name like '%' + @findcolumn +'%'
or sysobjects.name like '%' + @findcolumn +'%' )
order by TableFound,ColumnFound
end
Lowell
September 14, 2007 at 9:57 am
I found a script called sp_grep that does exactly that. I'm sure it is much like Lowell's script he posted.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 17, 2007 at 10:16 am
This is the code I used which works but can anybody offer suggestions to adjust this code to identify only key fields with a specified name? Here is my code:
Select o.[name] from sysobjects as o inner join syscolumns as c on o.id = c.id
where c.[name] = 'SomeSuspectedKeyFieldID' order by o.[name]
Thanks,
September 17, 2007 at 10:58 am
Here's mine:
-- Returns all tables that use a particular column. The specified column name
-- may contain valid SQL wildcards.
-- Examples:
-- exec GetTablesByCol 'id';
-- exec GetTablesByCol '%id';
create procedure GetTablesByCol (
@ColName nvarchar(128)
)
as
begin
set nocount on;
-- Works for 2000 & 2005
select Object_Name(id) as [Table],
name as [Column]
from syscolumns
where name like @ColName;
-- Works for 2005 "and beyoooooond!"
-- select Object_Name(object_id) as [Table],
-- name as [Column]
-- from sys.columns
-- where name like @ColName;
end--ProcedureTomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
September 17, 2007 at 11:13 am
This Sproc comes from Andrew Zanevsky. It does what I need it to do.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- sp_grep v1.0 03/16/1995, v1.1 10/26/1995
-- Author: Andrew Zanevsky, AZ Databases, Inc.
-- Internet: 71232.3446@compuserve.com */
ALTER PROC [dbo].[sp_grep] @PARAMETER VARCHAR(255) = null, @CASE CHAR(1) = 'i'
AS
/* Note: @case parameter default changed from 's' to 'i'
to support default case-insensitive comparision,
as practiced at TRS.
*/
SET NOCOUNT ON-- PVP - Disable interim communication with caller.
SET QUOTED_IDENTIFIER OFF-- PVP - Added for v7 ANSI compatability.
DECLARE @str_no TINYINT,
@msg_str_no VARCHAR(3),
@OPERATION CHAR(1),
@string VARCHAR(80),
@oper_pos SMALLINT,
@context VARCHAR(255),
@i TINYINT,
@longest TINYINT,
@msg VARCHAR(255)
IF @PARAMETER IS null /* provide instructions */
BEGIN
PRINT 'Execute sp_grep "{string1}operation1{string2}operation2{string3}…", [case]'
PRINT '- stringN is a string of characters up to 80 characters long, '
PRINT ' enclosed in curly brackets. Brackets may be omitted if stringN '
PRINT ' does not contain leading and trailing spaces or characters: +,-,&.'
PRINT '- operationN is one of the characters: +,-,&. Interpreted as or,minus,and.'
PRINT ' Operations are executed from left to right with no priorities.'
PRINT '- case: specify "i" for case insensitive comparison.'
PRINT 'E.g. sp_grep "alpha+{beta gamma}-{delta}&{+++}"'
PRINT ' will search for all objects that have an occurence of string "alpha"'
PRINT ' or string "beta gamma", do not have string "delta", '
PRINT ' and have string "+++".'
RETURN
END
/* Check for or characters */
IF CHARINDEX(CHAR(10), @PARAMETER) > 0 or CHARINDEX(CHAR(13), @PARAMETER) > 0
BEGIN
PRINT 'Parameter string may not contain or characters.'
RETURN
END
IF LOWER(@CASE) = 'i'
SELECT @PARAMETER = LOWER(LTRIM(RTRIM(@PARAMETER)))
ELSE
SELECT @PARAMETER = LTRIM(RTRIM(@PARAMETER))
CREATE TABLE #search (str_no TINYINT, OPERATION CHAR(1), string VARCHAR(80), last_obj INT)
CREATE TABLE #found_objects (id INT, str_no TINYINT)
CREATE TABLE #result (id INT)
/* Parse the parameter string */
SELECT @str_no = 0
WHILE datalength(@PARAMETER) > 0
BEGIN
/* Get operation */
SELECT @str_no = @str_no + 1, @msg_str_no = RTRIM(CONVERT(CHAR(3), @str_no + 1))
IF @str_no = 1
SELECT @OPERATION = '+'
ELSE
BEGIN
IF SUBSTRING(@PARAMETER, 1, 1) in ('+', '-', '&')
SELECT @OPERATION = SUBSTRING(@PARAMETER, 1, 1),
@PARAMETER = LTRIM(RIGHT(@PARAMETER, datalength(@PARAMETER) - 1))
ELSE
BEGIN
SELECT @context = RTRIM(SUBSTRING(@PARAMETER + SPACE(255 - datalength(@PARAMETER)), 1, 20))
SELECT @msg = 'Incorrect or missing operation sign before "' + @context + '".'
PRINT @msg
SELECT @msg = 'Search string ' + @msg_str_no + '.'
PRINT @msg
RETURN
END
END
/* Get string */
IF datalength(@PARAMETER) = 0
BEGIN
PRINT 'Missing search string at the end of the parameter.'
SELECT @msg = 'Search string ' + @msg_str_no + '.'
PRINT @msg
RETURN
END
IF SUBSTRING(@PARAMETER, 1, 1) = '{'
BEGIN
IF CHARINDEX('}', @PARAMETER) = 0
BEGIN
SELECT @context = RTRIM(SUBSTRING(@PARAMETER + SPACE(255 - datalength(@PARAMETER)), 1, 200))
SELECT @msg = 'Bracket not closed after "' + @context + '".'
PRINT @msg
SELECT @msg = 'Search string ' + @msg_str_no + '.'
PRINT @msg
RETURN
END
IF CHARINDEX('}', @PARAMETER) > 82
BEGIN
SELECT @context = RTRIM(SUBSTRING(@PARAMETER + SPACE(255 - datalength(@PARAMETER)), 2, 20))
SELECT @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
PRINT @msg
SELECT @msg = 'String begins with "' + @context + '".'
PRINT @msg
RETURN
END
SELECT @string = SUBSTRING(@PARAMETER, 2, CHARINDEX('}', @PARAMETER) - 2),
@PARAMETER = LTRIM(RIGHT(@PARAMETER,
datalength(@PARAMETER) - CHARINDEX('}', @PARAMETER)))
END
ELSE
BEGIN
/* Find the first operation sign */
SELECT @oper_pos = datalength(@PARAMETER) + 1
IF CHARINDEX('+', @PARAMETER) between 1 and @oper_pos
SELECT @oper_pos = CHARINDEX('+', @PARAMETER)
IF CHARINDEX('-', @PARAMETER) between 1 and @oper_pos
SELECT @oper_pos = CHARINDEX('-', @PARAMETER)
IF CHARINDEX('&', @PARAMETER) between 1 and @oper_pos
SELECT @oper_pos = CHARINDEX('&', @PARAMETER)
IF @oper_pos = 1
BEGIN
SELECT @context = RTRIM(SUBSTRING(@PARAMETER + SPACE(255 - datalength(@PARAMETER)), 1, 20))
SELECT @msg = 'Search string ' + @msg_str_no +
' is missing, before "' + @context + '".'
PRINT @msg
RETURN
END
IF @oper_pos > 81
BEGIN
SELECT @context = RTRIM(SUBSTRING(@PARAMETER + SPACE(255 - datalength(@PARAMETER)), 1, 20))
SELECT @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
PRINT @msg
SELECT @msg = 'String begins with "' + @context + '".'
PRINT @msg
RETURN
END
SELECT @string = SUBSTRING(@PARAMETER, 1, @oper_pos - 1),
@PARAMETER = LTRIM(RIGHT(@PARAMETER,
datalength(@PARAMETER) - @oper_pos + 1))
END
INSERT #search VALUES (@str_no, @OPERATION, @string, 0)
END
SELECT @longest = MAX(datalength(string)) - 1
FROM #search
/* —————————————————————— */
/* Search for strings */
IF @CASE = 'i'
BEGIN
INSERT #found_objects
SELECT a.id, c.str_no
FROM syscomments a, #search c
WHERE CHARINDEX(c.string, LOWER(a.text)) > 0
INSERT #found_objects
SELECT a.id, c.str_no
FROM syscomments a, syscomments b, #search c
WHERE a.id = b.id
and a.number = b.number
and a.colid + 1 = b.colid
and CHARINDEX(c.string,
LOWER(RIGHT(a.text, @longest) +
/* space(255 - datalength(a.text)) +*/
SUBSTRING(b.text, 1, @longest))) > 0
END
ELSE
BEGIN
INSERT #found_objects
SELECT a.id, c.str_no
FROM syscomments a, #search c
WHERE CHARINDEX(c.string, a.text) > 0
INSERT #found_objects
SELECT a.id, c.str_no
FROM syscomments a, syscomments b, #search c
WHERE a.id = b.id
and a.number = b.number
and a.colid + 1 = b.colid
and CHARINDEX(c.string,
RIGHT(a.text, @longest) +
/* space(255 - datalength(a.text)) +*/
SUBSTRING(b.text, 1, @longest)) > 0
END
/* —————————————————————— */
SELECT DISTINCT str_no, id INTO #dist_objects FROM #found_objects
CREATE UNIQUE CLUSTERED INDEX obj ON #dist_objects (str_no, id)
/* Apply one operation at a time */
SELECT @i = 0
WHILE @i < @str_no
BEGIN
SELECT @i = @i + 1
SELECT @OPERATION = OPERATION FROM #search WHERE str_no = @i
IF @OPERATION = '+'
INSERT #result
SELECT id
FROM #dist_objects
WHERE str_no = @i
ELSE IF @OPERATION = '-'
DELETE #result
FROM #result a, #dist_objects b
WHERE b.str_no = @i
and a.id = b.id
ELSE IF @OPERATION = '&'
DELETE #result
WHERE not exists
(SELECT 1
FROM #dist_objects b
WHERE b.str_no = @i
and b.id = #result.id)
END
/* Select results */
SELECT DISTINCT id INTO #dist_result FROM #result
/* The following select has been borrowed from the sp_help
** system stored procedure, and modified. */
SET NOCOUNT OFF-- PVP Display RowsAffected.
SELECT DISTINCT-- PVP Trim displayed list.
Name = o.name,
/* Remove 'convert(char(15)' in the following line
** if user names on your server are longer. */
Owner = CONVERT(CHAR(15), USER_NAME(uid)),
Object_type = SUBSTRING(v.name + ' ' + x.name, 1, 16)
FROM #dist_result d,
sysobjects o,
master.dbo.spt_values v,
master.dbo.spt_values x
WHERE d.id = o.id
/* SQL Server version 6.x uses 15, prior versions use 7 in expression below */
and o.sysstat & (7 + 8 * SIGN(CHARINDEX('6.', @@VERSION))) = v.number
and v.type = 'O'
and x.type = 'R'
and o.userstat & -32768 = x.number
ORDER BY Object_type , Name
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 17, 2007 at 5:27 pm
Good golly, folks... open Query Analyzer and press {f4}....
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2007 at 7:14 am
Thanks everyone, especially Jeff M. was not aware of this tool
September 18, 2007 at 7:36 am
Thanks, Mark... it takes DOS-like wildcards (*) as well...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply