SQL Query to find out specific column from all database
I wanted to find out the columns which contains the word "VAP" or cancellation. but the problem is we have several number of databases in our server. I wanted to know in which database/table contains the columns name which consist these words. Can anyone let us know if there is any query which can be used to search column name from all databases? I have tried below query but it will only help to find from a single database.
select distinct t.name as TableName, SCHEMA_NAME(t.schema_id) as TableSchema, c.name as ColumnName, ct.name as ColumnDataType, c.is_nullable as IsNullable from sys.tables t with(nolock) inner join sys.columns c with(nolock) on t.object_id = c.object_id inner join sys.types ct with(nolock) on ct.system_type_id = c.system_type_id where c.name like '%VAP%' order by t.name
If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).
It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??
How to find specific column in SQL Server database?, How do I find a specific column in SQL database? In this article, we will show how to write a SQL Query to Find all Tables that Contain Specific Column Name with example. For this SQL Interview Question, We are using the SQL Tutorial database. The below screenshot will show you the tables inside that database.
I know this is little lengthy, But You can use this query if you do not wish to use any third party applications
USE [master] GO DECLARE @Search VARCHAR(50)='A',@SQL VARCHAR(MAX) DECLARE @Min INT,@Max INT DECLARE @Table TABLE ( SeqNo INT IDENTITY(1,1), DatabaseNm VARCHAR(255), TableName VARCHAR(255), ColumnName VARCHAR(255) ) DECLARE @TEMP TABLE ( SeqNo INT IDENTITY(1,1), Qry VARCHAR(MAX) ) INSERT INTO @TEMP ( Qry ) SELECT Qry = 'SELECT DbNm = '''+name+''', TblNm = [TABLE_SCHEMA]+''.''+[TABLE_NAME], COlNm = COLUMN_NAME FROM ['+name+'].INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%'+ISNULL(@Search,'')+'%''' FROM sys.databases SELECT @Min = MIN(SeqNo), @Max = MAX(SeqNo) FROM @TEMP WHILE ISNULL(@Min,0)<=ISNULL(@Max,0) BEGIN SELECT @SQL = Qry FROM @TEMP WHERE SeqNo = @Min INSERT INTO @Table ( DatabaseNm, TableName, ColumnName ) EXEC(@SQL) SELECT @Min = ISNULL(@Min,0)+1 END SELECT * FROM @Table
Find all tables containing column with specified name, How do I find a specific column in all tables in SQL? Article for: SQL Server Azure SQL Database Oracle database MySQL PostgreSQL MariaDB IBM Db2 Amazon Redshift Snowflake Teradata Vertica Query below finds all tables that have 'ProductID' column. See also tables that don't have a column with specific name .
Use sp_MSForeachdb for getting details. Try below query.
------------------------- create table #temp (dbname varchar(100), tablename varchar(100) ) EXECUTE master..sp_MSForeachdb ' USE [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' BEGIN IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns where COLUMN_NAME = ''VAP'')) BEGIN insert into #temp select ''?'',table_name FROM INFORMATION_SCHEMA.columns where COLUMN_NAME = ''VAP'' end END ' select * from #temp
SQL SERVER, How do I find a particular column name in all tables in SQL Server? How can I find all columns of a certain type (for example NTEXT) in all tables in a SQL Server database? I am looking for a SQL query. This will also work on Azure SQL (aug 2018) and I used it to convert columns to nvarchar (max) because NText will be deprecated. alter table [tablename] alter column [columnname] nvarchar (max).
How to find column names for all tables in all databases in SQL Server, I quickly wrote down following script which will go return all the tables containing specific column along with their schema name. If you want to find all the column name from your database run following script. You can down any condition in WHERE clause to get desired result. Reference : Pinal Dave ( https://blog.sqlauthority.com )
Find Column in All Databases, Here is a basic example to get all columns in all databases: --This query will select the Database name, all tables/views and their columns (in a comma COLUMNS).sql -- Utility to find all columns in all databases or find specific with a like One row represents one column in a specific view in a database. Scope of rows: all columns in views in SQL Server database. Ordered by schema name, view name and column position.
List table columns in SQL Server database, Find Column in All Databases databases on a particular server for either columns with a specific name, or columns relating to a specific subject. It will then return you all the columns on the server that are named like the From there, it’s a simple matter of selecting the ColumnName and TableName of our results, and finally, of course, only looking up records where sys.columns.name is equal to our ColumnName string. However, this query will only find exact matches of the column name.