Find the table with the GUID as a name

Asked by At

I have tables with the GUID as their names which is gets generated.

For example:

I have the tables with names of:

FD962987-AA3E-441C-B608-2392B68A5287

508482AF-EFCF-41C1-B083-D51BF37C3590

And also having some other tables in the database.

I just want to find the table with the name as GUID.

My try:

SELECT name 
FROM sys.tables 
WHERE LEN(tables.name) = 36 

The above query works fine but when I have other tables(which are not GUID names) with the same len of table name getting displayed.

2 Answers

4
Pratik Bhavsar On Best Solutions

Try something like:

SELECT 
    name 
FROM 
    sys.tables 
WHERE 
    LEN(tables.name) = 36 
    AND (tables.name LIKE '%-%-%-%-%') --OR tables.name LIKE '%-____-____-____-%') 
2
LukStorms On

If your MS SQL Server version supports it (2012+) you could use TRY_CONVERT for this.
A TRY_CONVERT returns NULL when the conversion fails.

SELECT t.name 
FROM sys.tables t 
WHERE LEN(t.name) = 36 
  AND TRY_CONVERT(UNIQUEIDENTIFIER, t.name) IS NOT NULL;

Or you could use a LIKE with character ranges. But it's a rather anti-golfcode method.
Since a UNIQUEIDENTIFIER has a very specific format that only uses the characters ABCDEF0123456789 between 4 dashes.

SELECT t.name 
FROM sys.tables t 
WHERE t.name like '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]'