how to make a procedure to find and filter columns of a table

USE master

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--declare   @objname nvarchar(776) -- object name we're after

--    ,@fieldname varchar(50)

--set @objname = 'PI$Sales Line'

alter proc sp_qryTable

      @objname nvarchar(776) = null -- object name we're after

      ,@fieldname varchar(50) = null

as

declare     @dbname     sysname ,@no varchar(35), @yes varchar(35), @none varchar(35)

select @no = 'no', @yes = 'yes', @none = 'none'

-- @objname must be either sysobjects or systypes: first look in sysobjects

declare @objid int

declare @sysobj_type char(2)

set @dbname = db_name()

declare @t as table( oID int , oType varchar(100))

insert into @t

( oID  , oType )

exec( 'select  object_id, type from [' + @dbname + '].sys.all_objects where [' +  @dbname  + '].sys.all_objects.name = ''' + @objname + '''  ')

 select @objid = oID, @sysobj_type = oType from @t

-- select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname)

declare @numtypes nvarchar(80)

select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'

declare @fieldnameNull varchar(50)

if ( @fieldname is null)

begin

      set @fieldnameNull = 'null'

      set @fieldname = ''

end

else

begin

      set @fieldnameNull = 'notnull'

      --set @fieldname = ''

end

declare @sSQL varchar(max)

set @sSQL = '

            select

                  [Column_name]                 = name,

                  [Type]                        = type_name(user_type_id),

                  [Computed]                    = case when ColumnProperty(object_id, name, ''IsComputed'') = 0 then ''' + @no + ''' else ''' + @yes + ''' end,

                  [Length]                            = convert(int, max_length),

                  [Prec]                              = case when charindex(type_name(system_type_id), ''' + @numtypes + ''' ) > 0

                                                            then convert(char(5),ColumnProperty(object_id, name, ''precision''))

                                                            else ''     '' end,

                  [Scale]                             = case when charindex(type_name(system_type_id), ''' + @numtypes + ''' ) > 0

                                                            then convert(char(5),OdbcScale(system_type_id,scale))

                                                            else ''     '' end,

                  [Nullable]                    = case when is_nullable = 0 then ''' + @no + ''' else ''' + @yes  + ''' end

                 

                  from [' + db_name() + '].sys.all_columns

                  where object_id = ' + convert(varchar(20) , @objid  ) + '

                        and (name like ''%' + @fieldname  + '%'' or '''+  @fieldnameNull + ''' = ''null'' )

     

'

exec(@sSQL)

 -- select @sSQL