SQL Server how to select from a list of tables using information_schema.columns or sys.tables?
putting into Table_List all tables of the database as rows.
SELECT [table_name] INTO Table_List FROM INFORMATION_SCHEMA.TABLES
selecting from multiple tables with a table
SELECT [common column in each table ] FROM [Table_List]
This query doesn't work.
I also tried with putting the rows into a list of strings (table names) and then using FROM to select them. But didn't work as well.
Does someone know if there is a way to use the
SELECT * FROM tables names stored into a table or list?
I need this because the tables names or changing every month and are thus called with the sys.table or INFORMATION_SCHEMA.TABLES keywords?
Assuming I understand the question, you want to select all the values of a specific column from all the tables where this column exists. For that, you need to use dynamic SQL. Here is one way to do it:
DECLARE @Sql nvarchar(4000) -- You might need nvarchar(max) here SELECT @Sql = STUFF( ( SELECT ' UNION ALL SELECT Id, '''+ TABLE_NAME +''' As TableName FROM '+ TABLE_NAME FROM Information_schema.Columns WHERE Column_Name = 'ID' FOR XML PATH('') ), 1, 11, '') EXEC(@Sql)
This will return all Id values from all tables where there is an Id column, along with the table name where it exists.
Please note that it requires that all the
id columns will have the same data type, or at least data types that can be implicitly converted to each other.
Find all tables containing column with specified name, How do you find a table that contains a specific column in SQL Server? Column name Data type Description <inherited columns> For a list of columns that this view inherits, see sys.objects (Transact-SQL).: lob_data_space_id: int: A nonzero value is the ID of the data space (filegroup or partition scheme) that holds the large object binary (LOB) data for this table.
Because it is too complicated to solve this problem without real data, I will try to add some:
| tables 1 | table 2 | ... | table n --------------------------------------------------------------------------------------- columns_name: | name | B | C | D | name | B | C | D | ... | name | B | C | D ------------------------------------------------------------------------------------------ column_content:| John | ... | Ben | ... | ... | John| ...
The objective is to extract the rows in the N tables where name = 'John'.
Where we already have a table called [table_names] with the n tables names stored in the column [column_table_name].
Now we want to do something like that:
SELECT [name] FROM (SELECT [table_name] FROM INFORMATION_SCHEMA.TABLES) WHERE [name] = 'Jonh'
Tables names are dynamic and thus unknown until we run the information_schema.tables query.
This final query is giving me an error. Any clue about how to use multiple stored tables names in a subquery?
SQL Server List Tables: How to Show All Tables, How do I get a list of tables in SQL? Listing all the tables in SQL server when using a newer version (SQL 2005 or greater) is a matter of querying the INFORMATION_SCHEMA views which are automatically built into SQL Server. These allow you to easily view a wide variety of metadata for this particular SQL Server instance, including information about COLUMNS , ROUTINES , and even
if you want to fetch List of tables having common column name then you simply use
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name ='your_column_name'
If your requirement is not like that, then could you please give some example of your requirement. Right now i'm not much clear.
SQL 201: 6 Ways to Search with System Tables, You may notice that there are four columns returned when using the INFORMATION_SCHEMA.TABLES view, but the most important column is TABLE_TYPE , SQL command to list all tables in SQL Server. In SQL Server, you can use the following query to find all tables in the currently connected database: SELECT * FROM information_schema.tables; SQL command to list all tables in DB2. First, connect to a specific database on the DB2 database server: db2 connect to database_name
Zohar Beled, I tried your way and I feel I am getting closer to the solution. But I got a new error: "Could not find stored procedure 'SELECT * FROM , AFU$, 'APP 3$', 'ASH $', BICHAT$, 'TE APHP$', VS$, CFS$, CIOD$, CMCP$, CNCF$, COLLOQUE$, EADV$"
DECLARE @tableList nvarchar(4000) DECLARE @Mycommand nvarchar(4000) SET @tableList = (SELECT (CAST(', ' + [table_name] AS VARCHAR(MAX))) FROM [Table_List] FOR XML PATH ('')) SET @Mycommand = 'SELECT * FROM ' + @tableList EXEC @Mycommand
Learn SQL: The INFORMATION_SCHEMA Database, In SQL Server these are often referred to as system tables and views. sys.indexes - shows each index and type; information_schema.columns - shows This query returns a list of tables, in alphabetical order, with a count of the columns. By: Greg Robidoux Overview The INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database.
I also tried with this query to first put the rows into a comma-separated string and then use the new table containing the string in the FROM statement. But it doesn't give anything.
select STRING_AGG(table_name, ', ') as column_string into table_string from Table_List select * from (select column_string from table_string)
Search with system tables, list all databases on the server - using sys. or sp We'll list all tables in the database we've selected and also all constraints. we also have the TABLE_NAME column, which tells us which table this constraint is related to. SELECT resource_type,spid,login_time,status,hostname,program_name,nt_domain,nt_username,loginame FROM sys.dm_tran_locks dl JOIN sys.sysprocesses sp on dl.request_session_id = sp.spid The tables involved are master tables; how do I give permission to marie to read from them? I already tried to execute the following as dbo:
Database Security, This query returns a list of column names that match the search criteria in the WHERE clause. Add your Transact-SQL. -- SQL SQL Server. use mydatabase -- put your DB here. go. select join sys.tables tab on scol.object_id = tab.object_id information_schema.columns. By: Greg Robidoux Overview The INFORMATION_SCHEMA.COLUMNS view allows you to get information about all columns for all tables and views within a database. By default it will show you this information for every single table and view that is in the database.
SQL Server, MySQL SELECT Column_Name FROM information_schema .tables Returns a list syscolumns Returns a list of tables found in the Server sys.columns view of These filters will search for and block certain known SQL injection characters or An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables.
System Information Schema Views (Transact-SQL), Question: How to get column names from a specific table in SQL Server? Method 1: Using INFORMATION_SCHEMA.COLUMNS. 1. 2. 3. SELECT * Method 2: Using Sys Schema (My Preferred Way) Query below lists all tables in SQL Server database. Query select schema_name(t.schema_id) as schema_name, t.name as table_name, t.create_date, t.modify_date from sys.tables t order by schema_name, table_name; Columns. schema_name - schema name; table_name - table name; create_date - date the table was created
- "I need this because the tables names or changing every month" This is an indication that the database is poorly designed to begin with. Table names should not be changed.
- This query executed successfully and returns rows like: UNION ALL SELECT Prénom, 'BICHAT$' As TableName FROM BICHAT$ or UNION ALL SELECT Prénom, 'VS$' As TableName FROM VS$ But what I need is running some test on the content of the column [Prénom] on all tables having this column [Prénom] but without typing explicitly the tables name.
- I'm not really sure what you mean by that comment. The code I've shown generates an SQL statement containing the select statement, and then executes it using
exec(@sql). I though that is what you wanted...
- Ok, I think we are still getting closer to the solution. Now I need to add this SQL statement to a (Select ...From @Sql) statement. But first, I have an error when trying to execute the 'EXEC(@Sql): Msg 102, Level 15, State 1, Line 75 Incorrect syntax near '8.'. Msg 102, Level 15, State 1, Line 75 Incorrect syntax near '3'. Msg 102, Level 15, State 1, Line 75 Incorrect syntax near '$'. Msg 102, Level 15, State 1, Line 75 Incorrect syntax near 'APHP$'.
- It's impossible to debug code you don't see - but my guess is that you need to add
quotenameto the column names.
- What about replacing (SELECT @Sql) with (SET @Sql) and then write (SELECT * FROM @Sql)?
- What I need to do is retrieving some data stored in tables having the same column structure (those tables are tabs from an Excel File). The thing is that those tabs/table names are changing every month. I was able to create a query to store the tables name into a new table (with INFORMATION_SCHEMA.COLUMNS). But I can't use this table with the SELECT FROM statement.
- Since it's not a stored procedure, you need parenthesis -