Get All Tables Where Column Exist With Column Info

,

This procedure will get all tables with column information where column exist.
There is an option to search for exact or "like" name. By default it's exact name

if exists (select name from sysobjects 
			where name = '_GET_TABLES_FOR_COLUMN' AND type = 'P')
	drop procedure _GET_TABLES_FOR_COLUMN
GO
create proc _GET_TABLES_FOR_COLUMN
	@ColumnName Varchar(100),@Like char(1) = " "
AS

/*
This procedure will get all tables with column information where column exist
There is an option to search for exact or "like" name. By default it's exact name.
Use: _GET_TABLES_FOR_COLUMN 'client_code','y' (similar name)
   or _GET_TABLES_FOR_COLUMN 'client_code' (exact name)

Created 4/5/2002
*/
if len(ltrim(@Like)) <> 0 
	select @Like = '%'
SELECT @ColumnName = rtrim(ltrim(@Like + @ColumnName + @Like)) 
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.columns  
where COLUMN_NAME like @ColumnName
GROUP BY COLUMN_NAME,TABLE_NAME,COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,DATA_TYPE
ORDER BY COLUMN_NAME,TABLE_NAME,COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,DATA_TYPE


--_GET_TABLES_FOR_COLUMN 'client_code',"y"

Rate

5 (1)

Share

Share

Rate

5 (1)