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.

Feel free to inform me if you find any mistakes or if you know other useful SQL tips!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s