SQLTeam.com | Weblogs | Forums

Find columns in DB with Pattern


#1

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 :frowning:

Regards
Nicole :wink:


#2

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

#3

thanks :slight_smile: