January 9, 2012 at 12:58 am
HI ALL,
As i Have a task to find 9 digit value in all column of all table in a DB or All DB in SQL server 2005.
SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?
All Help is appreciated.
January 9, 2012 at 1:10 am
Ivan Mohapatra (1/9/2012)
HI ALL,As i Have a task to find 9 digit value in all column of all table in a DB or All DB in SQL server 2005.
SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?
All Help is appreciated.
You'll need to use dynamic SQL to query for the character columns, then in a cursor loop through those columns looking for the correct values.
January 9, 2012 at 1:13 am
hi u mean by using DMV query can u just Send me the query so that i can go through it and find the query
January 9, 2012 at 1:29 am
hi you may use the Len variable.
select employeeID, EmployeeName, EmployeeTask from Employees
where Len(employeedID) = 9
this will post only all employeeID whos lenght is 9 eg.(000333444)
hope this helps 🙂
cheers! 😀
===============================================================
"lets do amazing" our company motto..
January 9, 2012 at 2:01 am
in addition you may use this script.. 🙂
you may want to run this per database lol.. haha just change the value to what you want to search lol.. you get the idea? just tweek it some more and you might be able to modify it to the one you need. this will return the table where it was located.
EXEC SearchAllTables 'Computer'
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
===============================================================
"lets do amazing" our company motto..
January 9, 2012 at 2:01 am
i don'nt know the table & column
i just want to find table name and column name where data len = 9
can any one ping the script PLZ
January 9, 2012 at 2:26 am
i don't know the table name and column name So this query will not match my requirement.
January 9, 2012 at 2:28 am
mrdenny can u send me the script
January 9, 2012 at 8:27 am
You need to find table and column names for any table that contains a numeric value that is exactly 9 digits?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2012 at 8:33 am
yes exactly i want all the column name and table name where value is 9 character
January 9, 2012 at 8:46 am
What have you tried? It looks like the script posted above would be a pretty good starting point. That could be modified pretty easily to accomplish what you are after.
I have to ask, this is a rather strange request. What is the reason behind finding such information?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2012 at 8:46 am
Untested, but something like this should work
BEGIN TRAN
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + 'SELECT ' + c.name + ', '''+c.name+''' AS columnName FROM ' + o.name + ' WHERE LEN('+c.name+')=9;' + CHAR(10)
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.type = 'U' AND
t.name IN ('tinyint','smallint','int','real','money','float','decimal','numeric','smallmoney','bigint')
EXEC (@SQL)
ROLLBACK
January 9, 2012 at 8:52 am
because in our db there are table with wrong datatype.So i wan to find it by this means to complete a task
January 9, 2012 at 8:53 am
Cadavre (1/9/2012)
Untested, but something like this should work
BEGIN TRANDECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + 'SELECT ' + c.name + ', '''+c.name+''' AS columnName FROM ' + o.name + ' WHERE LEN('+c.name+')=9;' + CHAR(10)
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.type = 'U' AND
t.name IN ('tinyint','smallint','int','real','money','float','decimal','numeric','smallmoney','bigint')
EXEC (@SQL)
ROLLBACK
Let me try this but not sure it will work
January 9, 2012 at 9:57 am
Agree with Sean. It’s not like regular requirements that we get from management. For instance, if a table has 2 rows and one of the columns have 123456789 and 12345678 values. Would you consider this table and column as desired output?
What do you mean by wrong data type? Oversized columns? Don’t have data model of your database to figure it out? It would be a nice option, if you have.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply