January 9, 2012 at 10:42 am
HI all,
Well the point of discussion is to have a script by which i can get all the column name and table name in a db where the data length is 9 digit and the datatype should be varchar,int.AS because we have a ID column with datatype as varchar & Int in different table.
So as this is a rarer request in any IT forum IT will be a nice one for all to learn it from the giants how it is done.
So kindly help me out for which i will be very thankful to u all.
January 9, 2012 at 10:47 am
Dev (1/9/2012)
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.
hope it is you ( DEV PINAL) I Am a great Fan Of Your SKill.
yes I would consider that as desired out put.Because we don't have such data So As ID column has 9 digit only in my entire DB.
January 9, 2012 at 11:20 am
Ivan Mohapatra (1/9/2012)
Well the point of discussion is to have a script by which i can get all the column name and table name in a db where the data length is 9 digit
data length or data type?
and the datatype should be varchar,int.AS because we have a ID column with datatype as varchar & Int in different table.
The sentence (or fragment) above doesn't make any sense. What is "varchar,int.as"? Yo need to slow down and write a description that makes sense.
January 9, 2012 at 11:36 am
Steve Jones - SSC Editor (1/9/2012)
Ivan Mohapatra (1/9/2012)
Well the point of discussion is to have a script by which i can get all the column name and table name in a db where the data length is 9 digitdata length or data type? -Data length
and the datatype should be varchar,int.AS because we have a ID column with datatype as varchar & Int in different table.
The sentence (or fragment) above doesn't make any sense. What is "varchar,int.as"? Yo need to slow down and write a description that makes sense.
Below is a Example what i mean to say and my kind request kindly read my request seriously and understand it before u all reply.
Well there are 60 table in 1 Db there is a ID column in where there are 9 digit data.like 999999999,888888888
well in this 60 table there is around 20 column where 9 digit data.like 999999999,888888888 exits AND when i found there is two kind of datatype is been used 1 is varchar and another is INT for this ID column in different tables. i mean in one table the datatype for ID column is varchar and in another table ID column is INT.
So What i want is a script by which i can get all the table name and column name in this DB
where len of Data is 9 digit data.like 999999999,888888888 .
January 9, 2012 at 11:59 am
OK we are now 20 posts into your request and the details of what you want is still changing with each post. We can't see over your shoulder, we are not familiar with your system or what you want to retrieve out of it. There have been a number of people trying to help. You have to help us help you. Give us a detailed explanation of what you want.
So What i want is a script by which i can get all the table name and column name in this DB
where len of Data is 9 digit data.like 999999999,888888888 .
What does that mean? Do you want only varchar fields that a numeric value that has a length of 9? Does it count as a match if the column has other data?
Taking the first script posted you could change the where clause find what you are looking for.
pure pseudocode but...
where datalength(column) = 9 and isnumeric(coliumn) = 1
If you explain clearly (yes we know it is clear to you) in a way that somebody else can understand it, we will be happy to help you.
_______________________________________________________________
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 12:05 pm
Sean Lange (1/9/2012)
OK we are now 20 posts into your request and the details of what you want is still changing with each post. We can't see over your shoulder, we are not familiar with your system or what you want to retrieve out of it. There have been a number of people trying to help. You have to help us help you. Give us a detailed explanation of what you want.So What i want is a script by which i can get all the table name and column name in this DB
where len of Data is 9 digit data.like 999999999,888888888 .
What does that mean? Do you want only varchar fields that a numeric value that has a length of 9? Does it count as a match if the column has other data?
Taking the first script posted you could change the where clause find what you are looking for.
pure pseudocode but...
where datalength(column) = 9 and isnumeric(coliumn) = 1
If you explain clearly (yes we know it is clear to you) in a way that somebody else can understand it, we will be happy to help you.
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
SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?
this is what i want and
January 9, 2012 at 12:19 pm
A couple of things. Simply repeating a vague and non-descript explanation is not going to make it clear for anyone. It is obvious that you are getting frustrated. This is professional forum and as such we treat each other professionally. That means that you don't need to yell at anyone. Also, simply demanding that people provide the script is not going to happen. We are more than happy to share when it seems the person on the other end is trying to learn. Most people around here are reluctant to just hand over the goods to somebody who doesn't want to put in the effort for themselves. We are all volunteers around here and don't want to spend much time doing work for somebody else to reap the rewards. On the other hand, those of us who post around here do so because we truly enjoy passing on the knowledge we have gained through other people. Tutoring is very rewarding, being a consultant with no pay is not.
Here is a script I wrote a long time ago to find certain values throughout the database. It is not exactly what you are looking for but should prove to work for your needs with some pretty simple modification.
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
declare @SearchVal varchar(200)
set @SearchVal = '%your search val here%'
declare @ColName varchar (250)
set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'
declare SearchList cursor for
select distinct so.name,sc.name from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name like @ColName
and so.type = 'U'
open SearchList
fetch next from SearchList into @table_name, @column_name
while(@@fetch_status = 0)
begin
select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
You will just need to tweak the where clauses and you should be able to get this to work. I would warn you about running this in production because the performance is totally horrible. Given that you have to search each and every column and row in the entire database it is not possible to make this fast. I would run this against a restored copy of production instead of crippling your production box.
_______________________________________________________________
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 12:24 pm
Use a script like this: http://www.sqlservercentral.com/scripts/Miscellaneous/65769/
Change the where from a "column like @value" to "datalength(column) = 9"
If you want 9 digit values.
You think you're asking for something simple, but either we have a language barrier or you don't understand what you're asking, because what you're posting doesn't make sense in English. It doesn't provide a description that can be translated to code.
January 9, 2012 at 11:08 pm
This SQL query will help to find table name and column name in database which column value length is 9.
DECLARE @Tab_Name VARCHAR(500)
DECLARE @Col_Name VARCHAR(500)
DECLARE @SQL NVARCHAR(MAX)
CREATE TABLE #TMP_Details(TabName VARCHAR(500) NOT NULL,ColName VARCHAR(500) NOT NULL)
DECLARE CUR_GET_INFO CURSOR FAST_FORWARD FOR
SELECT DISTINCT SO.name,SC.NAME FROM SYS.OBJECTS SO
INNER JOIN SYS.COLUMNS SC ON SO.object_id=SC.object_id
WHERE SO.type='U'
OPEN CUR_GET_INFO
FETCH NEXT FROM CUR_GET_INFO INTO @Tab_Name,@Col_Name
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL='IF EXISTS (SELECT 1 FROM '+@Tab_Name+' WHERE LEN('+@Col_Name+')=9)'
SET @SQL=@SQL+'BEGIN INSERT INTO #TMP_Details VALUES('''+@Tab_Name+''','''+@Col_Name+''') END'
EXEC (@SQL)
FETCH NEXT FROM CUR_GET_INFO INTO @Tab_Name,@Col_Name
END
CLOSE CUR_GET_INFO
DEALLOCATE CUR_GET_INFO
SELECT * FROM #TMP_Details
DROP TABLE #TMP_Details
January 10, 2012 at 3:53 am
You may also want to try the following code; modified it to what I believe is what you want--I originally created this one to detect all TEXT fields in a database:
if exists (select * from tempdb..sysobjects where name like '#fieldinfo%')
begin
drop table #fieldinfo
end;
SelectTABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, CASE WHEN data_type = 'uniqueidentifier' THEN 36
ELSE isnull(character_maximum_length, isnull(numeric_precision, datetime_precision))
END as charlength_or_precision
, isnull(collation_name, isnull(cast(numeric_scale as varchar(10)), 'n.a.')) as collation_or_scale, is_nullable
into #fieldinfo
From INFORMATION_SCHEMA.COLUMNS
WhereDATA_TYPE in ('varchar', 'int')
and isnull(character_maximum_length, isnull(numeric_precision, datetime_precision)) = 9
select *
from #fieldinfo
Order By TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
Let us know if this helped to resolve your issue.
Regards,
Michael
January 10, 2012 at 5:12 am
The script from jnuqui should give you what you need...
January 10, 2012 at 9:03 am
Ivan Mohapatra (1/9/2012)
Dev (1/9/2012)
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.
hope it is you ( DEV PINAL) I Am a great Fan Of Your SKill.
yes I would consider that as desired out put.Because we don't have such data So As ID column has 9 digit only in my entire DB.
It’s a huge complement but I am not the one you are looking for. He is ‘Pinal Dave’ and you can find him on http://blog.sqlauthority.com/. IIRC he does post in his blog ONLY. 2000+ articles till date…
January 11, 2012 at 8:10 am
Querying the INFORMATION_SCHEMA views can return columns with definitions that matche search criteria, and then you can also use a similar query to dynamically build other SQL queries against those same table columns. I'm not sure what exactly you're requesting, but I think your solution can be derived from the examples below.
select t.table_schema+'.'+t.table_name table_name, c.column_name,
c.data_type+'('+cast(c.character_maximum_length as varchar(4))+')' data_type
from information_schema.tables t
join information_schema.columns c
on c.table_name = t.table_name
where t.table_type = 'base table'
and c.data_type = 'varchar'
and c.character_maximum_length >= 9;
table_namecolumn_namedata_type
dbo.sysmail_mailitemsbody_formatvarchar(20)
dbo.sysmail_mailitemssensitivityvarchar(12)
dbo.sysmail_mailitemsattachment_encodingvarchar(20)
select 'select max('+c.column_name+') max_'
+t.table_name+'_'+c.column_name+' from '
+t.table_schema+'.'+t.table_name+';'
from information_schema.tables t
join information_schema.columns c
on c.table_name = t.table_name
where t.table_type = 'base table'
and c.data_type = 'varchar'
and c.character_maximum_length >= 9;
select max(body_format) max_sysmail_mailitems_body_format from dbo.sysmail_mailitems;
select max(sensitivity) max_sysmail_mailitems_sensitivity from dbo.sysmail_mailitems;
select max(attachment_encoding) max_sysmail_mailitems_attachment_encoding from dbo.sysmail_mailitems;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 11, 2012 at 11:42 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchAllTablesAllColumns]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT
SELECT @TableName = '', @ColumnName = ''
SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')
WHILE (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName = (
SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)
+ '|' + QUOTENAME(C.Column_name))
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND C.DATA_TYPE IN ('varchar')
AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName
)
SET @Parse = PATINDEX ('%|%', @ColumnName)
SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', SUBSTRING('
+ @ColumnName + ',1, 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
)
END
END
SELECT ColumnName, ColumnValue FROM #Results where len(ColumnValue)= '9'
ORDER BY ColumnName
END
----------------
Can any one suggest in this script -well i have modified this script which was provided by
jnuqui. well i want a int value to be searched but when ever i am adding INT in C.DATA_TYPE IN ('varchar','INT')
IT is troughs error in the when i pass a Int value to be searched. error as (Invalid argument for string).So can any one modify or suggest how i can fix this and make this work .
January 12, 2012 at 3:59 am
Dev (1/10/2012)
Ivan Mohapatra (1/9/2012)
Dev (1/9/2012)
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.
hope it is you ( DEV PINAL) I Am a great Fan Of Your SKill.
yes I would consider that as desired out put.Because we don't have such data So As ID column has 9 digit only in my entire DB.
It’s a huge complement but I am not the one you are looking for. He is ‘Pinal Dave’ and you can find him on http://blog.sqlauthority.com/. IIRC he does post in his blog ONLY. 2000+ articles till date…
OK DEV NO ISSUE IF U CAN HELP ME OUT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchAllTablesAllColumns]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT
SELECT @TableName = '', @ColumnName = ''
SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')
WHILE (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName = (
SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)
+ '|' + QUOTENAME(C.Column_name))
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND C.DATA_TYPE IN ('varchar')
AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName
)
SET @Parse = PATINDEX ('%|%', @ColumnName)
SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', SUBSTRING('
+ @ColumnName + ',1, 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
)
END
END
SELECT ColumnName, ColumnValue FROM #Results where len(ColumnValue)= '9'
ORDER BY ColumnName
END
----------------
Can any one suggest in this script -well i have modified this script which was provided by
jnuqui. well i want a int value to be searched but when ever i am adding INT in C.DATA_TYPE IN ('varchar','INT')
IT is troughs error in the when i pass a Int value to be searched. error as (Invalid argument for string).So can any one modify or suggest how i can fix this and make this work .
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply