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.<\/p>\n
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.<\/p>\n
To find these two things I have written two stored procedures to use as follows
:<\/p>\n
sp_FindColumns \u2018Code\u2019 sp_FindInProcedure \u2018Tax_Code\u2019<\/span><\/p>\n .And below are the defenitions for them.<\/p>\n ——————————————————————————– <\/span><\/p>\n if exists (select * from sysobjects Create Procedure sp_FindColumns SELECT SO.name + \u2018.\u2019 + Sc.name END<\/p>\n ——————————————————————————– Create Procedure sp_FindTables END<\/p>\n if exists(select * from sysobjects where id = object_id (N\u2018[dbo].[sp_FindInProcedure]\u2019) and Create Procedure sp_FindInProcedure BEGIN SELECT so.name, sc.text END<\/p>\n","protected":false},"excerpt":{"rendered":" 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 developersput their application initials as a prefix of the table names and as a result wegot several Contact tables and several Items each with diffeent layouts. When I am dealing … Continue reading “Searching tables and columns”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[97],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"post-thumbnail":false},"uagb_author_info":{"display_name":"Pouya Panahy","author_link":"http:\/\/www.panahy.nl\/index.php\/author\/pouya\/"},"uagb_comment_info":0,"uagb_excerpt":"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 developersput their application initials as a prefix of the table names and as a result wegot several Contact tables and several Items each with diffeent layouts. When I am dealing…","_links":{"self":[{"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/posts\/120"}],"collection":[{"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/comments?post=120"}],"version-history":[{"count":0,"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/posts\/120\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/media?parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/categories?post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.panahy.nl\/index.php\/wp-json\/wp\/v2\/tags?post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
And
sp_FindTables \u2018Code\u2019
<\/span>
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:<\/p>\n
where id = object_id(N\u2018[dbo].[sp_FindColumns]\u2019) and OBJECTPROPERTY(id,
N\u2018IsProcedure\u2019) = 1)
drop procedure [dbo].[sp_FindColumns]
GO<\/p>\n
@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
**************************************************************************************\/ <\/span><\/p>\n
FROM SysColumns Sc
Inner Join SysObjects SO
on SO.ID = SC.ID
Where SO.xtype=\u2018U\u2019
And Sc.Name like \u2018%\u2019 + @sToSearch + \u2019%\u2019<\/p>\n
<\/span>
if exists (select * from sysobjects
where id = object_id(N\u2018[dbo].[sp_FindTables]\u2019) and OBJECTPROPERTY(id,
N\u2018IsProcedure\u2019) = 1)
drop procedure [dbo].[sp_FindTables]
GO<\/p>\n
@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
*************************************************************************************<\/span>*\/ <\/span>
SELECT Distinct So.Name
FROM SysColumns Sc
Inner Join SysObjects SO on SO.ID = SC.ID
Where So.xtype= \u2019U\u2019
And SO.Name like \u2018%\u2019 + @sToSearch + \u2018%\u2019<\/p>\n
OBJECTPROPERTY(id, N\u2018IsProcedure\u2019) = 1) color=”#0000ff\u201d
drop procedure [dbo].[sp_FindInProcedure]
GO<\/p>\n
@sToSearch
varchar(255)
AS<\/p>\n
\/************************************************************************************* *
* 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
**************************************************************************************\/<\/span><\/p>\n
FROM syscomments sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.xtype = \u2018P\u2019
And sc.Text like \u2019%\u2019 + @sToSearch + \u2018%\u2019
Order By so.Name Asc<\/p>\n