Check if Column or Table exists in SQL DB

Posted by admin on Mar 30, 2010 in SQL, tips |
SQL Code

SQL Code

This is a very useful script which I use all of the time. Especially if there are issues around Database Schema maps being kept up to date.

This SQL script will take a string as an input and output all of the column names and table names where that string is used.

So for instance you have a column called username however its not in the obvious place which would be the users table.  You know the column exists as you’ve seen it trace outputs or the developer has said to you “check the users column.

You would run the below script and would get a list of any Tables or Columns with a name like ‘users’

– Author : Martin Hall
– Date 12th June 2009
– Search Table or Column for text
– To search for a TableName then just change the “where syscolumns” to “where sysobjects”)

SELECT     syscolumns.name AS [Col Name], sysobjects.name AS [Table Name]
FROM         syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE     (syscolumns.name LIKE N’%users%’)
Order by sysobjects.name

Hope you find it useful.

Tags:

1 Comment


[...] so just like the post below about searching for columns or table names. This post is all about how to search inside Stored Procedures for strings / [...]


 

Reply

Copyright © 2012 The Test Manager Blog All rights reserved. Theme by Laptop Geek.