Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: Select from a list of tables and columns

    Hello all,

    the following returns all base tables within the database of type "varchar":

    Code:
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns 
    WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb' 
    AND DATA_TYPE IN('varchar')"
    AND TABLE_NAME IN(
    	SELECT TABLE_NAME FROM mydb.information_schema.tables 
    	WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
    What I then want to do is...
    for each of these results:
    Code:
    select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME]
    WHERE ID = 'test'
    Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?

    Kind Regards,
    Grant

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not clear about what you want... I never thought that I'd ever request this, but can you ask the question in English instead of Transact-SQL?

    It would appear that you want to scan your entire database for columns that contain the string 'test', but I'm not completely sure that's what you really meant.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2014
    Posts
    5
    You can use a cursor to pull through the result set of the first statement. For each row, you can build a SQL statement with string functions and concatenation, then execute it with dynamic SQL. There might be a better way to do what you want, if you can be more particular about your question. The mechanism I describe make the pendants cringe, but is certainly viable.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    DECLARE @SearchString VARCHAR(100) = 'test';
    
    SELECT C.TABLE_SCHEMA, 
    	C.TABLE_NAME, 
    	C.COLUMN_NAME,
    	'SELECT ' + C.COLUMN_NAME + ', * ' +
    	'FROM ' + C.TABLE_CATALOG + '.' + C.TABLE_SCHEMA + '.' + C.TABLE_NAME + ' ' + 
    	'WHERE ' + C.COLUMN_NAME + ' = ''' + @SearchString + ''';' as SQL_cmd
    FROM test.information_schema.columns AS C
    	INNER JOIN test.information_schema.tables AS T ON
    		C.TABLE_NAME = T.TABLE_NAME
    		AND T.TABLE_TYPE = 'BASE TABLE'
    		AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    		AND C.TABLE_CATALOG = T.TABLE_CATALOG
    WHERE C.TABLE_SCHEMA = 'master' 
    	AND C.TABLE_CATALOG = 'mydb' 
    	AND C.DATA_TYPE IN ('varchar')
    ;
    Output:
    Code:
    SELECT Name, * FROM mydb.master.contract WHERE Name = 'test';
    It selects all tables with a VARCHAR column in it. Then generates the SQL that searches for records that have such a column with a value equal to @SearchString.

    The WHERE ID = 'test' made no sense to me, I changed that into WHERE <varchar_column> = 'test'. In our databases the id's are INT or BIGINT, never VARCHAR.

    You can select the SQL_cmd column in the result and cut-and-paste them into SSMS to execute them. I wouldn't go into the dynamic SQL lane any further than this.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •