Hi,
How can I find all the rows of a database that have a particular pattern: for example: (INC000007615432) or with other words:
Find all columns in a Database, where Column-Properties:
Len (15) = char (15)
Starts with "INC"
After INC following 12 num digits
I have over 100 tables with many columns, and i can not take a look in every table. No Documentation, Columns have diffrent Names
I would mechanically generate an SQL statement from the system tables (to get the Table and Column names). Only need to check CHAR / VARCHAR / N... datatype columns that are 15 chars or more.
Something like this perhaps
SELECT
'PRINT ''' +
+ S.name
+ '.' + T.name
+ '.' + C.name
+ ''''
+ CHAR(13)+CHAR(10) + 'GO'
+ CHAR(13)+CHAR(10)
+ 'SELECT TOP 100 * FROM '
+ QuoteName(S.name)
+ '.' + QuoteName(T.name)
+ ' WHERE LEN(' + QuoteName(C.name) + ') = 15'
+ ' AND ' + QuoteName(C.name) + ' LIKE ''INC%'''
+ ' AND ' + QuoteName(C.name) + ' NOT LIKE ''INC%[^0-9]%'''
+ CHAR(13)+CHAR(10) + 'GO'
FROM sys.tables AS T
JOIN sys.schemas AS S
ON S.schema_ID = T.schema_ID
JOIN sys.columns AS C
ON C.object_ID = T.object_ID
JOIN sys.types AS TY
ON TY.user_type_id = C.user_type_id
WHERE C.max_length >= 15
AND TY.name IN ('char', 'nchar', 'varchar', 'nvarchar')
--
ORDER BY T.name, S.name, C.name
I've limited the output from any given table to 100 rows so it doesn't go "berserk" if a column has lots of matching rows !!
Sample output:
PRINT 'dbo.MyTable.MyColumn'
GO
SELECT TOP 100 *
FROM [dbo].[MyTable]
WHERE LEN([MyColumn]) = 15
AND [MyColumn] LIKE 'INC%'
AND [MyColumn] NOT LIKE 'INC%[^0-9]%'
GO