Find Table an Column name

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  January 22, 2008 at 11:47 am

  #770592

  Unless you have things like triggers in place to track when things are updated - no, there really isn't any built-in way to know when tables are updated (actually one might argue that triggers aren't built-in, so it might be better to say simply that there are NO built-in ways, just ways for you set up nofitication/track what was updated).

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  January 23, 2008 at 8:29 am

  #770940

  I am not sure why am not getting the result as expected though am trying to test with known result.

  Hey guys have you tried doing this, is it working?

  everytime I exec i get an invalid object error for all the tables.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 23, 2008 at 10:26 am

  #770980

  it might be that all your objects are not owned by dbo.

  you might have tables that need to be referenced by mike.tablename or webdev.tablename, for example .... instead of just tablename.

  are you loging in as administrator/sa?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• EvilPostIT

  SSCrazy

  Points: 2578

  More actions

  January 23, 2008 at 10:39 am

  #770983

  --Here is a schema friendly version.

  ---------------------------------

  declare @cmd nvarchar(4000)

  declare @srchstr nvarchar(200)

  declare @tbl nvarchar(255)

  declare @col nvarchar(255)

  create table ##results (res varchar(255))

  select @srchstr='ALFKI' --CHANGE THIS TO YOUR SEARCH STRING

  declare structure cursor for

  select table_schema+'.['+table_name+']',column_name from information_schema.columns

  where data_type like '%char%'--YOU WIDEN THE RANGE OF DATA TYPES SEARCHED BY CHANGING THIS

  open structure

  fetch next from structure into @tbl,@col

  while @@fetch_status=0

  begin

  select @cmd='if (select count(*) from '+@tbl+' with (nolock) where ['+@col+']='''+@srchstr+''')>0

  begin

  insert into ##results values(''Data Found in table ['+@tbl+'] column ['+@col+']'')

  end'

  print @cmd

  exec sp_executesql @cmd

  fetch next from structure into @tbl,@col

  end

  select * from ##results

  drop table ##results

  close structure

  deallocate structure

  ---

  
  Nuke the site from orbit, its the only way to be sure... :w00t: