Find is_ansi_padded status of columns

SELECT b.name as 'schema_name', a.name as 'table_name', a.type_desc

       , c.name, c.column_id

       , d.name as [type_name], c.max_length, c.precision

       , c.scale, c.is_nullable

       , c.is_ansi_padded, is_identity

FROM sys.types d INNER JOIN (sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b 

    ON a.schema_id = b.schema_id)

              ON c.object_id = a.object_id)

              ON d.user_type_id = c.user_type_id

WHERE b.name like '%snapshot%'

       and is_ansi_padded = 0 

        and d.name NOT IN ('int', 'datetime', 'date', 'numeric', 'float', 'bigint', 'smallint')

ORDER BY a.[type], b.name, a.name, c.column_id

To fix

TrimTrailingBlanks "no" is the same as ANSI_PADDING ON.  So it looks like those columns were (correctly) created with SET ANSI_PADDING ON.

If you have only a few columns with TrimTrailingBlanks "yes", you can create a new column, migrate data and rename like the example below, keeping in mind that you will need do drop constraints and indexes on the column(s) and recreate afterward.  You might find it easier to recreate the table if you have many columns.

SET ANSI_PADDING ON;
GO
ALTER TABLE dbo.MyTable
    ADD MyColumn_New varchar(100);
GO
UPDATE dbo.MyTable
    SET MyColumn_New = MyColumn;
GO
ALTER TABLE dbo.MyTable
    DROP COLUMN MyColumn;
GO
EXEC sp_rename 'dbo.MyTable.MyColumn_New', 'MyColumn', 'COLUMN';
GO

T-SQL no leading zeroes

DECLARE @Sku NVARCHAR(500)

    SET @Sku = '000000000000000000000000000000000000000000000000000000012335670000'

 SELECT @Sku AS Original,

        SUBSTRING(@Sku,PATINDEX('%[^0]%',@Sku),DATALENGTH(@Sku)) AS NoLeadingZeros

Declare @MyStr char(20)

Set @MyStr = '00004567890'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

What this does is to find the first non-zero character in the field and then substring from there.  It also works if you have a dash imbeded in the field.

Declare @MyStr char(20)

Set @MyStr = '0000-45670'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

Make comma separated list from database table

select distinct se.guid, spt.respid
into #tempSUBLOS
from dbo.Static_Pivot_Table spt inner join dbo.tblSaratogaExtract se on spt.respid=se.respid
where spt.sublos is null


DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + se.[GUID]
from #tempSUBLOS se
SELECT @listStr

drop table #tempSUBLOS
GO

GRANT EXECUTE

exec [dbo].[GSPrimeExcel]

GRANT EXECUTE ON [dbo].[GSPrimeExcel] TO GSPrimeExcelUser;

------------------

USE DB1;
GRANT SELECT ON OBJECT:: View1 TO JohnDoe;
GO

DateTime into Date

, CONVERT(char(10), DateOfHire,126)


turns DateTime in to Date

Create List of Calendar Dates

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Holidays_Weekends](
CalendarDate datetime NOT NULL,
descr [varchar](20) NULL,
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


WITH sample AS (
 SELECT CAST('2015-04-01' AS DATETIME) AS dt
 UNION ALL
 SELECT DATEADD(dd, 1, dt)
FROM sample s
  WHERE DATEADD(dd, 1, dt) <= CAST('2015-06-30' AS DATETIME))

INSERT INTO [dbo].[Holidays_Weekends]
SELECT *, '' FROM sample

SQL function DataLength

SELECT CompanyName
, len(CompanyName) as 'length'
, datalength(CompanyName) as 'RealLength'
FROM TempfinanceCompanyList 

WHERE right(Companyname,1) = ' '
ORDER by CompanyName

You cannot count on LEN function to count trailing spaces, at least not in SQL 2012.

Compare two SQL Server tables

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.

UNION allows you to compare all columns very quickly, and also handles comparing NULL values to other NULLs successfully, which a join clause or a WHERE condition doesn't normally do.  It also allows you to very quickly see which rows are missing in either table, which only a FULL OUTER JOIN will do, but of course we all know to avoid those at all costs (right?) -- a full outer join is about as “unrelational” as you can get.  (every column returned is potentially Null and must be wrapped in a COALESCE function).  Best of all, the UNION is quick and easy and short.

The basic idea is: if we GROUP the union of two tables on all columns, then if the two tables are identical all groups will result in a COUNT(*) of 2.  But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want.  We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.

So, here's an example, assuming we are comparing tables A and B, and the primary key of both tables is ID:

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
  SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
  FROM A
  UNION ALL
  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
  FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID