Finding a Column with a Specific Name in TSQL

I just recently ran into a situation where I needed to find a table that contained a specific column in the database. I’m not looking for a column value, I’m looking for the name of the column. So, like any good programmer would do, I spent some time Googling…

There were a number of unhelpful articles and answers on the subject. I sifted through everything and finally landed upon an answer that worked! Unfortunately I forgot where it came from, so if any of you know where this little trick came from feel free to let me know.

In order to find a column with a specific name in TSQL you can use the following query:

USE [YOUR_DATABASE_NAME]
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%[WHAT YOU ARE LOOKING FOR]%'
ORDER BY schema_name, table_name;

Just replace [YOUR_DATABASE_NAME] with the name of the database you are using and [WHAT YOU ARE LOOKING FOR] with the full column name or a part of the column name you are looking for.

Alternative to Eval() and Casting in Databound Control

Some of you might already know this, however I found it interesting and helpful. For the case of this example let’s say you have a Person object defined somewhere in your ASP.NET code.

Public Class Person
	Public Property FirstName As String
	Public Property MiddleName As String
	Public Property LastName As String

	Public ReadOnly Property FullName As String
		Get
			Return String.Join(" ", FirstName, MiddleName, LastName)
		End Get
	End Property
End Class

Say for example you have a collection of Persons that you’ve assigned to a webforms repeater. In your ItemTemplate you would like to access the properties of whatever object you’ve bound to the repeater. .NET 4.5 introduced some new options that allow you to strongly type your ASP.NET data controls.

First let’s go through the way it used to be done…

.aspx.vb File (CodeBehind)

'// declare a list for our repeater to use
Dim persons As List(Of Models.Person)

'// populate the list with persons (returned by this random function, who cares what it does)
persons = _personDAO.GetPersons()

'// assign the list to the repeater and bind 'em up
repPeople.DataSource = persons
repPeople.DataBind()

.aspx File

<asp:Repeater ID="repPeople" runat="server">
	<HeaderTemplate><ul class="people"></HeaderTemplate>
	<ItemTemplate>
		<li class="col_12 peopleWrap">
			FullName: <%# Eval("FullName")%>
		</li>
	</ItemTemplate>
	<FooterTemplate></ul></FooterTemplate>
</asp:Repeater>

You can use Eval to evaluate a string and output the related value like is done in the example above. This uses reflection to figure out what the current object is and outputs whatever property that you’ve specified in your string. This is just a string so you don’t get any intellisense and you have to remember (ugghhh – remembering what’s that?) what properties you have access to.

You can also cast the object to the type you know that it should be. This is nice because it avoids expensive reflection and it also gives us intellisense. After casting you can access the properties normally with intellisense help. (Not to mention warnings and errors should a property on the source object ever change). I.E. Full Name: <%# CType(Container.DataItem, Models.Person).FullName%>
Casting is nice and all, but it can get a bit tedious and messy, especially if you have to cast every time you want to access a property.

Using .NET 4.5 — Strongly Typed Repeaters
However, thankfully, .NET 4.5 decided to help us out and allow us to strongly type our repeaters. This gives us the best of both worlds, we get intellisense, avoid expensive reflection, and also can easily access properties without extensive use of CType. All you have to do is set the ItemType attribute on the repeater itself.

<asp:Repeater ID="repPeople" runat="server" ItemType="Models.Person">
	<HeaderTemplate><ul class="people"></HeaderTemplate>
	<ItemTemplate>
		<li class="col_12 peopleWrap">
			FullName: <%# Item.FullName%>
		</li>
	</ItemTemplate>
	<FooterTemplate></ul></FooterTemplate>
</asp:Repeater>

Dig Web Interface ftw!

Ok. I guess because this is my first post on here I should say something like, “Hi Reader, thanks for stopping by and reading my blog.” So I guess I will.

Hi Reader, thanks for stopping by and reading my blog.

I just wanted to mention really quick how awesome digwebinterface.com is. I’ve used it so many times to find DNS information about different domain names out there. For example, I just now confirmed the DNS information was correct for this blog, jeremysawesome.com.

Confirming DNS with digwebinterface.com
Yep, the DNS for jeremysawesome.com looks correct.

DNS confirmed, jeremysawesome.com is ready to rock!