3

In my database I have created some tables whose name contains '_' character in middle, I want to find those all tables.
for example:

Doc_Amit,Doc_Raj,chem_Man etc

I know the query for finding table name as below:

select * from sys.tables where name like '%%'

but my question is what should i write between % and % so that I only get table names which contains wild card character '_' ??

Note : I am using SQL Server 2012

Gray
  • 7,050
  • 2
  • 29
  • 52
Mudassir
  • 187
  • 1
  • 9

2 Answers2

5

Try this one - 

SELECT s.name + '.' + o.name
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.name LIKE  '%\_%' ESCAPE '\'
    AND o.[type] = 'U'

Answer for comment -

    SELECT * 
    FROM sys.objects o

    -> 

ALTER VIEW sys.objects AS
    SELECT *
    FROM sys.objects$

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

    SELECT * 
    FROM sys.tables t

    ->

ALTER VIEW sys.tables AS
    SELECT *
    FROM sys.objects$ o
    LEFT JOIN sys.sysidxstats lob ON lob.id = o.object_id AND lob.indid <= 1
    LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.object_id AND ds.class = 8 AND ds.depsubid <= 1   -- SRC_INDEXTOLOBDS 
    LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.object_id AND rfs.class = 42 AND rfs.depsubid = 0   -- SRC_OBJTOFSDS
    LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option
    WHERE o.type = 'U'
Devart
  • 119,203
  • 23
  • 166
  • 186
  • Yeah, I got the correct result.This can be used as an alternative.Thanks for the same ! – Mudassir Apr 15 '13 at 12:34
  • Forgot to say. The system table sys.tables contains many hidden JOIN statements, so select from sys.objects with additional filter by type should be faster. – Devart Apr 15 '13 at 12:41
  • 2
    My pedantic side wants to mention that sys.tables is a catalog view, not a system table. – Aaron Bertrand Apr 15 '13 at 12:43
  • Little mistake :). I agree with you. However, when we only need get name/object_id/schema_id. Desirable to select from sys.objects. It really increase the query performance. – Devart Apr 15 '13 at 12:56
  • I don't understand all the code you've added. I was just clarifying that in your comment you called sys.tables a "system table." As for the performance, well, I think if you know it's a table you're better off using sys.tables, this keeps the code slightly cleaner. If you can demonstrate a noticeable performance difference using sys.objects, and you are running this code often enough that it really makes a difference. Finally, why not use `SCHEMA_NAME()` instead of another join to `sys.schemas`? – Aaron Bertrand Apr 15 '13 at 13:01
  • 1
    I simple add description for system objects stored in db. So now you can see that the sys.tables has a lot join's. Usually, I use SCHEMA_NAME() when return 1 row, in our case possible return many rows, so it's best to do join with sys.schemas. – Devart Apr 15 '13 at 13:11
  • 3
    You're doing a metadata query. If it returns in 22ms or 28ms do you really care? – Aaron Bertrand Apr 15 '13 at 13:13
  • 2
    Yes, I care about every millisecond :). So if it possible make a request faster - it is desirable to do. Нope I helped you. – Devart Apr 15 '13 at 13:18
  • 1
    Well, if you want to waste minutes saving a few milliseconds once, go ahead. I would focus performance tuning efforts on real queries, not administrative / metadata one-offs. – Aaron Bertrand Apr 15 '13 at 17:25
1

I got the easiest ans of it, and its simpler !!

select * from sys.objects where name like '%[_]%'
Mudassir
  • 187
  • 1
  • 9