July 10, 2012 at 8:17 am
Howzit peeps
Im new to forum and new to sql so excuse for my inexperienced knowledge and terms, please correct me where im wrong...
I tried searching the forum for similar posts as my query maybe im just blind 🙂
Basically i have a data base with a whole bunch of tables, I was wondering is there a query I can run or filter out so I can search my entire database to for a specific column/string/variable dunno exactly what its called.
Like say I want to know what tables in my database contain a column called name for example...
Thanks in advanced:cool:
ill continue browsing all the threads maybe i can find what im looking for
July 10, 2012 at 8:23 am
try the sys.columns view. http://msdn.microsoft.com/en-us/library/ms176106.aspx
July 10, 2012 at 8:25 am
Try this: http://msdn.microsoft.com/en-us/library/ms345522.aspx#_FAQ30
MWise
July 10, 2012 at 8:26 am
...or INFORMATION_SCHEMA.COLUMNS
I'd also recommend installing SQL Search which is a free tool from Redgate. That will find things quickly, even if you don't know exactly what type of object they are.
July 10, 2012 at 8:35 am
sp_find
this home made procedure is probably my most used proc on my dev server!
too many times i think to myself "i know there are columns or tables that have the word "account" in them, which ones are they?
sp_find account gives me a fast visible list in SSMS of any tablename or column name that contains 'account';
this is a timesaver when you need to search the meta data like that.
hope this helps you out; i added it to my SSMS keyboard shortcuts, because i use it constantly.
CREATE PROCEDURE [dbo].[sp_find]
@findcolumn varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT
TableFound,
ColumnFound
FROM
(
SELECT
1 AS SortOrder,
sysobjects.name AS TableFound,
'' AS ColumnFound
FROM sysobjects
WHERE sysobjects.xtype IN('U' ,'V')
AND sysobjects.name LIKE '%' + @findcolumn + '%'
UNION ALL
SELECT
2 AS SortOrder,
sysobjects.name AS TableFound,
syscolumns.name AS ColumnFound
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id=syscolumns.id
WHERE sysobjects.xtype IN('U' ,'V')
AND syscolumns.name like '%' + @findcolumn + '%'
) X
ORDER BY
SortOrder,
TableFound,
ColumnFound
END
Lowell
July 10, 2012 at 11:47 am
If we are talking about searching for tables specifically here then you could use these:
The first one, usp_findColumn_thisDB, takes a parameter and searches all tables with a column matching that parameter.
For example, running this:
usp_findColumn_thisDB 'fact_sales'
Will find all tables that with a column named 'fact_sales'.
If you don't provide a parameter, all columns will be returned.
The second one, usp_findColumn_thisDBs, does the same thing but for all databases.
usp_findColumn_thisDB
/*
Created on 7/10/2012 by AJB
xmlsqlninja.com
Searches the informationschema for the column named passed by @search
If no value is provided then all columns will be returned in order of table/column name
*/
CREATE PROC usp_findColumn_thisDB
(
@searchvarchar(200) = '*All*'
)
AS
BEGIN
SET NOCOUNT ON;
IF object_id('tempdb..#columnNames') IS NOT NULL
DROP TABLE #columnNames;
CREATE TABLE #columnNames
(
[Table]nvarchar(200),
[Column]nvarchar(200)
)
BEGIN
INSERT INTO #columnNames
SELECTTABLE_CATALOG + '.' +
TABLE_SCHEMA + '.' +
TABLE_NAME AS [Table],
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
END
SELECT[Table],[Column]
FROM #columnNames
WHERE [Column] = @search OR @search='*All*'
ORDER BY [Table],[Column]
DROP TABLE #columnNames;
END
GO
usp_findColumn_allDBs:
/*
Created on 7/10/2012 by AJB
xmlsqlninja.com
Searches the informationschema IN EACH NON-SYSTEM DATABASE for the column named passed by @search
If no value is provided then all columns will be returned in order of table/column name
*/
CREATE PROC usp_findColumn_allDBs
(
@searchvarchar(200) = '*All*'
)
AS
BEGIN
SET NOCOUNT ON;
IF object_id('tempdb..#columnNames') IS NOT NULL
DROP TABLE #columnNames;
CREATE TABLE #columnNames
(
[Table]nvarchar(200),
[Column]nvarchar(200)
)
EXEC sp_msForEachDB
'
USE [?];
IF (SELECT TOP 1(TABLE_CATALOG)
FROM INFORMATION_SCHEMA.COLUMNS)
NOT IN (''master'', ''model'', ''tempdb'', ''msdb'')
BEGIN
INSERT INTO #columnNames
SELECTTABLE_CATALOG + ''.'' +
TABLE_SCHEMA + ''.'' +
TABLE_NAME AS [Table],
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
END
'
SELECT[Table],[Column]
FROM #columnNames
WHERE [Column] = @search OR @search='*All*'
ORDER BY [Table],[Column]
DROP TABLE #columnNames;
END
GO
-- Itzik Ben-Gan 2001
July 10, 2012 at 11:53 am
I also concur with Richard Warr's post. Redgate has a great tool for searching databases and the contents of databases (something worth noting, especially on SQLServerCentral.)
-- Itzik Ben-Gan 2001
July 10, 2012 at 12:56 pm
Beginning with SQL Server 2005, they included the INFORMATION_SCHEMA which is a better way to access this kind of information.
For columns you can use INFORMATION_SCHEMA.COLUMNS which, unlike sys.columns, provides the table, schema, column, data type, etc...
-- Itzik Ben-Gan 2001
July 10, 2012 at 1:03 pm
Simple query
DECLARE @ColName Varchar(128)
SET @ColName = 'yourcolumntosearch'
Select t.name as TableName, c.name as ColumnName, ty.name as DataType, c.max_length
,'('+ convert(varchar,c.precision) +','+ convert(varchar,c.scale)+')' as Precision_Scale
,d.definition as DefaultConstraint
,c.collation_name
From sys.tables t
Inner Join sys.columns c
on t.object_id = c.object_id
Inner Join sys.types ty
on c.system_type_id = ty.system_type_id
Left Outer Join sys.default_constraints d
on d.parent_object_id = c.object_id
and d.parent_column_id = c.column_id
WHERE c.NAME = @ColName
Order By t.name,c.column_id;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 10, 2012 at 1:06 pm
XMLSQLNinja (7/10/2012)
Beginning with SQL Server 2005, they included the INFORMATION_SCHEMA which is a better way to access this kind of information.For columns you can use INFORMATION_SCHEMA.COLUMNS which, unlike sys.columns, provides the table, schema, column, data type, etc...
I find that the INFORMATION_SCHEMA views lacking in information that I can get directly from the system views.
July 11, 2012 at 1:16 am
Thanks for all the reply's helped me alot, im still kinda noobish with all the query's and stuffs still playing around with it... still confuses the living daylights outa me but not gna give up.... Downloaded sql search works like a charm
Thank you very much really appreciate all the help 🙂
July 11, 2012 at 7:12 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply