MS Access: Drop Table Function if Exists

I had to check whether a specific table is already available before creating it through Access VBA script. This is very frequent. This code helped me.

Public Function ifTableExists(tablename As String) As Boolean

ifTableExists = False
If DCount("[Name]", "MSysObjects", "[Name] = '" & tablename & "'") = 1 Then
ifTableExists = True
Else
ifTableExists = False
End If

End Function

After creating above UserDefined Function it can be used as

If ifTableExists("MyTable") Then db.Execute "DROP Table MyTable"

Difference between Stored Procedures and Functions

Stored Procedure (SP)	Function (User Defined Function - UDF)
SPs are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called	Functions are compiled and executed every time when it is called
SP can return zero, single or multiple values	Function should return atleast one value
We can use Transactions in SP	Cannot use transactions in UDF
SPs can use SELECT, UPDATE, INSERT, DELETE, DROP statements	Functions are computed values and only SELECT statement is valid
SPs cannot be utilised in SELECT statements	Functions can be embedded in SELECT statement
SP can have input and output parameter	UDFs can have only input parameters
SPs can can execute with or without parameters	UDFs needs at least one input parameter. Without parameter UDFs can be created but it would be a insignificant one
We call function from SP	We cannot call SP from an UDF
Exception handlers like TRY-CATCH blocks can be used in SP	Cannot use Exception handlers can be used in UDF

MS Excel: Remove question mark inside box character

I had an excel file where one column had junk characters like question mark inside a box as in below screenshot. I needed to remove the junk. I tried Find-Repace option, trim, Substitute etc. None of these formulas worked. Then I found the function =CLEAN() to remove junks. It worked fine.

SQL Server: Trim all columns of a table at a time

I had a situation of trimming all columns of a table. My table had 80 columns. So I had to specify each column in the query to trim which was very hectic and irritating.

UPDATE mytable SET col1 = LTRIM(RTRIM(col1)), col2 = LTRIM(RTRIM(col1))... till col80

I found below script as the solution to avoid this.

USE MyDatabase

DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'mytable'

SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
EXEC (@SQL)

When you need to pass table name whcih has to perform LTRIM and RTRIM for all columns just create a stored procedure

CREATE PROCEDURE TrimAllColumnsOfTable @TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
    AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

If you have different schema names apart from default dbo schema like [abc].[TableName] and [xyz.pqr].TableName etc. then you must use below SP

CREATE PROCEDURE [dbo].[TrimAllColumnsOfTable] @SchemaName Varchar(100),@TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
    AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

We can use this script to trim all char,nchar, varchar and nvarchar columns of all tables across all databases in a server

SELECT 'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + 'SET [' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + '])) ' + 'WHERE [' + COLUMN_NAME + '] <> LTRIM(RTRIM([' + COLUMN_NAME + ']))' + CHAR(13) + CHAR(10) + 'GO' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'nvarchar') ORDER BY TABLE_NAME, COLUMN_NAME