SQL Resources

On this page I will collect SQL scripts that I use regularly and might be useful for others as well.

Scripts

Remove leading 0’s from a string

Can be modified to remove any leading values

Use “” in SSIS:

REPLACE(LTRIM(REPLACE([ColumnName],"0"," "))," ","0")

Use ” in SQL Management Studio:

REPLACE(LTRIM(REPLACE([ColumnName],'0',' ')),' ','0')

Find all tables containing a column with a certain name

Tested on SQL Server 2008 and 2012

When working with big databases (that someone else created) I often come into the situation that I want to find tables that contain columns with a certain name.

This works for tables:

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyName%'

And this includes views as well:

SELECT COLUMN_NAME AS ColName, TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%MyName%'

I found this useful code on stackoverflow.