Searching tables and columns

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

Column Description in SQL Server

The biggest challenge for me to learn de the business is to figure out how it hasbeen translated into a database. The more information I get out of the databasethe more familiar I feel myself with the business.
For applications living or quite some times and evolved by several developers, thisbecomes difficault if you can’t find out what those columns meant to keep. Speciallywhen they are keeping codes and flags which has not direct meaning for the businessbut rather they are stored as a meta data.
One of the handy implementation for such an information is the database itself.Lots of database administrators (and also developers) choose to keep the descriptionof a column somewhere reserved by the database. SQL-Server is not so convinientin this matter. Or maybe the system is while the user interface is not so open forit.
There is a so called undocumented stored procedure in the SQL-Server called sp_columns_rowset which shows you al you need and even more about atable. I am not going to talk about my experience with this undocumented storedprocedure which you can search the net and find some examples about it. Just mentioningit that it givs you something it calls DESCRIPTION of the field.
The question I have is where is this DESCRIPTION maintained? or even better, Howcan I fill this attribute? What should I do to get the descriptions of the columnsupdated with meaningfull sentence?