none
Find Company Names in Database RRS feed

  • Question

  • Hi All,

    I'm try to create a query that will identify columns in a database which contain company names. To do this I want to sample the first 1000 columns in every table for specific criteria (e.g. LIKE *PLC, LIKE *LLP etc) Can anyone advise how I could do this

    Thanks

    Robert 

    Tuesday, December 5, 2017 9:03 PM

All replies

  • You have a lot of tables with 1000 columns?

    I'm assuming you mean first 1000 rows of each column? I guess you'll have to bring back the 1000 rows first and then do the examination

    with cte as (
      select top 1000 * from table1
    )
    select * from cte where col1 like '%plc' or col1 like '%llp'

    etc

    Tuesday, December 5, 2017 9:33 PM
  • Hi Robert,

    According to your description, from every table, you would like to identify the columns which contains company names.(e.g. LIKE  *PLC, LIKE *LLP, etc.) For this purpose, here is the example code for your reference, see if it works for you.

    CREATE TABLE [dbo].[ACT_LIST1](
    	[SEQ_NO] [varchar](18) NOT NULL,
    	[LIST_G_NO] [numeric](9, 0) NOT NULL,
    	[INNER_NO] [varchar](64) NULL,
    	[MANUAL_NO] [varchar](12) NULL,
    	[CONTROL_MA] [varchar](10) NULL,
    	[G_MARK] [varchar](1) NULL,
    	[COP_G_NO] [varchar](500) NULL,
    	[CODE_T] [varchar](8) NULL,
    	[CODE_S] [varchar](2) NULL,
    	[G_NAME] [varchar](300) NULL,
    	[G_MODEL] [varchar](300) NULL,
    	[G_ENG_NAME] [varchar](300) NULL,
    	[G_ENG_MODEL] [varchar](300) NULL,
    	[ENT_UNIT] [varchar](16) NULL,
    	[UNIT] [varchar](4) NULL,
    	[UNIT_1] [varchar](4) NULL,
    	[UNIT_2] [varchar](4) NULL,
    	[QTY] [numeric](18, 5) NULL,
    	[QTY_2] [numeric](18, 5) NULL,
    	[FACTOR_1] [numeric](18, 5) NULL,
    	[FACTOR_2] [numeric](18, 5) NULL,
    	[FACTOR_WT] [numeric](18, 5) NULL,
    	[FACTOR_RATE] [numeric](18, 5) NULL,
    	[LEFT_QTY] [numeric](18, 5) NULL,
    	[REF_27] [numeric](18, 5) NULL,
    	[REF_28] [numeric](18, 5) NULL,
    	[REF_29] [numeric](18, 5) NULL,
    	[REF_30] [numeric](18, 5) NULL,
    	[REF_31] [numeric](18, 5) NULL,
    	[REF_32] [numeric](18, 5) NULL,
    	[REF_33] [numeric](18, 5) NULL,
    	[REF_34] [numeric](18, 5) NULL,
    	[REF_35] [numeric](18, 5) NULL,
    	[EXTEND_FIELD1] [varchar](70) NULL,
    	[EXTEND_FIELD2] [varchar](70) NULL
    ) ON [PRIMARY]
    
    insert into ACT_LIST1 ([SEQ_NO],[LIST_G_NO],[G_NAME],[G_MODEL])
    values('S',1,'xxxx','xPLC')
    insert into ACT_LIST1 ([SEQ_NO],[LIST_G_NO],[EXTEND_FIELD1],[EXTEND_FIELD2])
    values('S',2,'xxLLPx','xPLC')
    
    select * from ACT_LIST1
    
    declare @v_table_name varchar(128)
    declare @v_case_columns varchar(max)=''
    declare @v_condition_columns varchar(max)
    declare @v_ex_sql varchar(max)
    declare @v_top_num int
    
    set @v_table_name='ACT_LIST1'
    set @v_top_num=100
    
    set @v_case_columns=stuff((select  top (@v_top_num) ','+  'case when '+QUOTENAME(COLUMN_NAME)+' like ''%PLC%'' OR '+QUOTENAME(COLUMN_NAME)+' like ''%LLP%'' then 1 else 0 end as '+QUOTENAME(COLUMN_NAME)
    from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE in ('varchar','nvarchar') and TABLE_NAME=@v_table_name 
    order by ORDINAL_POSITION for xml path('')),1,1,'')   
    --print @v_case_columns
    
    set @v_condition_columns=stuff((select  top (@v_top_num) 'or'+QUOTENAME(COLUMN_NAME)+'>0 '
    from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE in ('varchar','nvarchar') and TABLE_NAME=@v_table_name 
    order by ORDINAL_POSITION for xml path('')),1,2,'')
    
    set @v_condition_columns=replace(@v_condition_columns,'>','>')
    --print @v_condition_columns
    
    set @v_ex_sql=';with cte as (
    select '+@v_case_columns+' from '+@v_table_name+')
    select * from cte where '+@v_condition_columns
    --print @v_ex_sql
    
    exec(@v_ex_sql)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Visakh16MVP Wednesday, December 6, 2017 4:13 AM
    Wednesday, December 6, 2017 3:25 AM
    Moderator
  • you can use INFORMATION_SCHEMA.COLUMNS or sys.columns view for this. Just look for condition column like '%PLC' etc

    However I feel like your full requirement is something different. Can I ask for what purpose you're doing this? May be there's a better way we can suggest, if you give us the full picture


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, December 6, 2017 4:15 AM
  • Hello Robert,

    See Search for a string in all tables of SQL Server Database for a dynamic & flexible solution.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, December 6, 2017 5:22 AM
  • Thanks it to identify columns with client information. I would like to sample data from all columns and then return a list for the columns with there tables that contain the data
    Friday, December 15, 2017 4:23 PM
  • So are you trying to find data within columns that match the pattern?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 15, 2017 5:12 PM
  • 1000 columns?! Wow! The widest table I ever saw in 30+ years of doing this was about 250 columns; it was one column for the results of individual allergy tests for some poor bastard who is getting checked for everything on earth.

    Perhaps more to the point, is that you ought to know which columns can have a company name in them. Why are you looking to determine something that should've been part of your design?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 15, 2017 8:28 PM