On this page I will collect SQL scripts that I use regularly and might be useful for others as well.
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.