LIKE operator

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  August 30, 2010 at 10:59 am

  #97575

  Hi,

  I am trying to remove all the unwanted tables ,sps,views in MYDB.The names we kept for our tables and sps will not be in number so i tried to get them separately,so i tried but i couldnt in single query ,the below is my requirement

  Select * from sys.sysobjects where Xtype='u' and name not like ('%1%','%2%','%3%')

  Select * from sys.sysobjects where Xtype='p' and name not like ('%1%','%2%','%3%')

  Select * from sys.sysobjects where Xtype='V' and name not like ('%1%','%2%','%3%')

  Thanks

  Parthi

  Thanks
  Parthi

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 30, 2010 at 11:17 am

  #1213850

  don't forget the LIKE operator can take some quasi regular expression commands to help determint ehte list:

  --every proc/view/table that DOES NOT contain a number.

  select * from sys.objects where name like '%[^0-9]%' and type in('U','V','P')

  --every proc/view/table that DOES contain a number.

  select * from sys.objects where name like '%[0-9]%' and type in('U','V','P')

  --every proc whihc contains a X y or z in the name

  select * from sys.objects where name like '%[x-z,X-Z]%' and type in('U','V','P')

  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!