You must Sign In to post a response.
  • Category: SQL Server

    Need to get the table name which is having particular data.

    HI Friends,

    I need to get all the table names and field names from a particular DB which contains a word as "DOTNET".

    Field name may differ from each table.

    Any idea?

    Thanks in advance.
  • #765211

    Hi Murugesan,

    Refer below link this might be helpful to you
    http://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database


    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #765223

    with the help of 'SYS.COLUMNS' and 'SYS.OBJECTS' you can get information about all columns and tables in particular databases.
    'INFORMATION_SCHEMA' table has also information stored per table
    see below Queries
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
    COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
    DATETIME_PRECISION
    FROM INFORMATION_SCHEMA.COLUMNS

    OR
    SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE], CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
    FROM SYS.OBJECTS AS T
    JOIN SYS.COLUMNS AS C
    ON T.OBJECT_ID=C.OBJECT_ID
    JOIN SYS.TYPES AS P
    ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
    WHERE T.TYPE_DESC='USER_TABLE';


    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765226
    Hi Prasad,

    Is it possible to get the column which have the data as "Ram".

    There may be 5 or 10 columns have the data like Ramkumar, Ramki, Ramesh, etc,.

    Here i have to find all the columns which have data "Ram". using LIKE.

    Thanks in advance.

  • #765251
    Practically it is not possible to fire LIKE on all columns to search specific word in sql table, but there is a way to fire a sub query that can work for you, see below snippet

    select
    YourTable.*
    FROM YourTable
    JOIN
    (
    select
    id,
    ISNULL(column1,'')+ISNULL(Column2,'')+...+ISNULL(ColumnN,'') concatenated
    FROM YourTable
    ) T ON T.Id = YourTable.Id
    where t.concatenated like '%x%'

    Here you can replace your columns with 'columnname' and then test the query, it should work.
    But it may kill your performance too.

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765420
    Hi Prasad,

    Here i dont know the column name.
    My condition is like, there is list of tables in DB, and i want to find all the table and column which contains the word RAJ. simply i want o find whether the word is present in the DB or not. If yes have to find the table and column name. I don't know the column names.


    Thanks in advance

  • #765434

    Hi,

    Use the below SQL script for find a word in sql database


    DECLARE
    @search_string VARCHAR(100),
    @table_name SYSNAME,
    @table_schema SYSNAME,
    @column_name SYSNAME,
    @sql_string VARCHAR(2000)

    SET @search_string = 'DOTNET'

    DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

    OPEN tables_cur

    FETCH NEXT FROM tables_cur INTO @table_schema, @table_name

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL -- Only strings have this and they always have it

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@column_name) + ''''

    EXECUTE(@sql_string)

    FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
    END

    CLOSE tables_cur

    DEALLOCATE tables_cur

    -- Siva

  • #765471
    sp_columns will get columns of a tablename
    sp_helpdb databasename results all the tables on particular database.

    You can filter through the datatable and find the particular table.

    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #765545
    Hi,,
    Try this one..

    declare @tableName varchar(100), @colName varchar(100) declare @sqlStatement nvarchar(100)

    declare tablelist cursor for select s.name, c.name from sys.columns c inner join sys.tables s on s.object_id = c.object_id where c.name like'%YourSearchCondtion%' OPEN tablelist FETCH NEXT FROM tablelist into @tableName, @colName

    while @@FETCH_STATUS = 0 BEGIN

    SELECT @sqlStatement = 'SELECT ' + @colName + ', * FROM ' + @tableName + ' WHERE ' + @colName + ' NOT LIKE ''%RAJ%'''

    exec sp_executesql @sqlStatement

    -- Here you can get the table that you dont want and add to a temp table.. PRINT CAST(@@ROWCOUNT AS VARCHAR)

    FETCH NEXT FROM tablelist INTO @tableName, @colName END

    CLOSE tablelist DEALLOCATE tablelist GO

  • #765556
    here in below link you will find the query to search data in tables
    <a href="http://crackyourinterview.com/Ads-Query-to-search-Data-in-any-Sql-table.aspx">Click here to get above question answer</a>

  • #765568
    Hi Murugesan

    Try this

    Select a.name as [Table Name], c.name as [Column Name]
    From sys.tables as a
    Inner Join sys.columns c on a.OBJECT_ID = c.OBJECT_ID
    Where c.name Like '%dotnet%'
    Order By [Table Name]


Sign In to post your comments