I am working with some big databases having similar tables for different group.
It seems to be poluted by different parties and have no administrator. Some developers
put their application initials as a prefix of the table names and as a result we
got several Contact tables and several Items each with diffeent layouts.
When I am dealing with these tables I need to find everything to do with lets say
Code. I need to find out all the tables having a field like Tax_Code or TaxCode
or ItemCode etc. It might be even good to know if there are tables called like Meta_Code
or Item_Code etc.
To find these two things I have written two stored procedures to use as follows
:
sp_FindColumns ‘Code’
And
sp_FindTables ‘Code’
When I was using these two I realised that also some stored procedures
are dealing with changing a field internally. So I had to also write something to
search the definitions of the stored rocedures to find al those mentioning the field
in the code and I came up with the following procedure:
sp_FindInProcedure ‘Tax_Code’.And below are the defenitions for them.
-------------------------------------------------------------------------------- if exists (select * from sysobjects
where id = object_id(N‘[dbo].[sp_FindColumns]’) and OBJECTPROPERTY(id,
N‘IsProcedure’) = 1)
drop procedure [dbo].[sp_FindColumns]
GO
Create Procedure sp_FindColumns
@sToSearch varchar(255)
AS
BEGIN
/************************************************************************************* *
* sp_FindColumns
*
* This stored procedure will look into all table collumns
* search the string which is given as the only parameter.
*
************************************************************************************* * By Asghar Panahy
* 12-jun-2006
**************************************************************************************/ SELECT SO.name + ‘.’ + Sc.name
FROM SysColumns Sc
Inner Join SysObjects SO
on SO.ID = SC.ID
Where SO.xtype=‘U’
And Sc.Name like ‘%’ + @sToSearch + ’%’
END
--------------------------------------------------------------------------------
if exists (select * from sysobjects
where id = object_id(N‘[dbo].[sp_FindTables]’) and OBJECTPROPERTY(id,
N‘IsProcedure’) = 1)
drop procedure [dbo].[sp_FindTables]
GO
Create Procedure sp_FindTables
@sToSearch varchar(255)
AS
BEGIN
/************************************************************************************* *
* sp_FindTables
*
* This stored procedure will look into all table names
* search the string which is given as the only parameter.
*
************************************************************************************* * By Asghar Panahy
* 12-jun-2006
**************************************************************************************/ SELECT Distinct So.Name
FROM SysColumns Sc
Inner Join SysObjects SO on SO.ID = SC.ID
Where So.xtype= ’U’
And SO.Name like ‘%’ + @sToSearch + ‘%’
END
if exists(select * from sysobjects where id = object_id (N‘[dbo].[sp_FindInProcedure]’) and
OBJECTPROPERTY(id, N‘IsProcedure’) = 1) color="#0000ff”
drop procedure [dbo].[sp_FindInProcedure]
GO
Create Procedure sp_FindInProcedure
@sToSearch
varchar(255)
AS
BEGIN
/************************************************************************************* *
* sp_FindInProcedure
*
* This stored procedure will look into all stored procedures defenitions to
* search the string which is given as the only parameter.
*
************************************************************************************* * By Asghar Panahy
* 12-jun-2006
**************************************************************************************/SELECT so.name, sc.text
FROM syscomments sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.xtype = ‘P’
And sc.Text like ’%’ + @sToSearch + ‘%’
Order By so.Name Asc
END
