Today, a colleague asked the following question: I want to know a bit about the data. I want to know which table of the database contains the data field. The first thing I thought of was to use a cursor, so I wrote the following statement, using a nested cursor, and the query was implemented. Write down it and I hope there is a better way to stay!
Declare @ Word Nvarchar ( 20 )
Set @ Word = ' Full '
-- Retrieve all database table names
Declare Tables Cursor For
Select Name From Sysobjects Where Xtype = ' U ' And Objectproperty (ID, n ' Isusertable ' ) = 1 And Name <> ' Dtproperties '
Declare @ Tablename Sysname
Open Tables
Fetch Next From Tables Into @ Tablename
While ( @ Fetch_status = 0 )
Begin
-- Retrieve all column names and types in a table
Declare Cols Cursor For
Select C. Name field, T. Name type From Syscolumns C Left Join Policypes t On C. xtype = T. xusertype Where C. ID = Object_id ( @ Tablename )
Declare @ Columnname Nvarchar ( 20 )
Declare @ Columntype Nvarchar ( 20 )
Open Cols
Fetch Next From Cols Into @ Columnname , @ Columntype
While ( @ Fetch_status = 0 )
Begin
Declare @ SQL Nvarchar ( 500 )
-- The image type cannot be converted to the character type. If other types are found, you can add conditions.
If ( @ Columntype <> ' Image ' )
Begin
Set @ SQL = ' Declare @ count int; select @ COUNT = count (1) from [ ' + @ Tablename + ' ] Where convert (nvarchar (100 ),[ ' + @ Columnname + ' ]) Like ''' + ' % ' + @ Word + ' % '' ; ' +
' If (@ count> 0) print ''' + @ Tablename + '' + ' ( ' + '' + @ Columnname + ' ) '''
Exec ( @ SQL )
End
Fetch Next From Cols Into @ Columnname , @ Columntype
End
Close Cols
Deallocate Cols
Fetch Next From Tables Into @ Tablename
End
Close Tables
Deallocate Tables
Output in northwind:
Employees (lastname)
Employees (photopath)
