Practical stored Procedure bis

Stored procedure practical stored procedure bis
The company that I work with is SQL Server database, every day to deal with a large number of data, because the author into the company's time later, most of the company's existing procedures are left by the previous programmers, because they do not have related documents, the author of the background database of many table structure and data are not very understanding, To the day-to-day maintenance caused a lot of trouble.

In the process of studying the background database, I need to get some relevant information about the database, for example, the company's database has a few tables to store the author's personal data, such as personnel table, Payroll, Department table and so on, but specifically which tables, it is not very clear, if you want to find a table, may not be able to find the dawn, So I decided to do a generic stored procedure that would iterate through all the character fields in the current database, find out exactly what tables and fields were to find the strings, and list them. For example, the name field of the personnel table, the Salary_name field of the salary table, the Employe_name field of the department table have the author's name, I hope to find out these. The stored procedure is as follows:

IF EXISTS (SELECT name from sysobjects

WHERE name = ' Searchname ' and type = ' P '

DROP PROCEDURE Searchname

Go

CREATE PROCEDURE searchname @sname varchar (10)

As

Begin

CREATE TABLE #TableList (

TableName char (200),

ColName char (200)

)



DECLARE @table varchar (200)

DECLARE @col varchar (200)



SET NOCOUNT ON

DECLARE curtab scroll cursor for select name from sysobjects where xtype= ' u '

Open Curtab

FETCH NEXT from Curtab into @table

While @ @FETCH_STATUS =0

Begin

DECLARE curcol scroll cursor for select name to Syscolumns where (xtype=175 or xtype=167) and (ID in (SELECT ID from sys objects where name= @table))

Open Curcol

FETCH NEXT from Curcol into @col

While @ @FETCH_STATUS =0

Begin

Execute (' INSERT into #TableList select ' + @table + ' ", ' + @col + ' ' from ' + @table + ' where ' + @col + ' = ' + @sname + ' ')

FETCH NEXT from Curcol into @col

End

Close Curcol

Deallocate Curcol

FETCH NEXT from Curtab into @table

End

Close Curtab

Deallocate Curtab

SET NOCOUNT OFF

SELECT DISTINCT * from #TableList

drop table #tablelist

End

The call is simple, if you want to find the author's name, call Searchname ' forgot2000 ', the lookup speed depends on the size of the current database. Hopefully this stored procedure will help. I e-mail:coolforgot@sina.com,qq:33563255, hope to share with you, thank you!

This stored procedure is passed under sqlserver7.0/2000.