SQL Snippet

SQL Server: How do look at the dataypes/lengths of columns in a temp table?
To view that information, there are 2 primary ways:select * from tempdb.INFORMATION_SCHEMA.C... where table_name like '#MyTempTable%'select * from tempdb.sys.columns where object_id = object_id('tempdb..#mytempt... ......

Posted On Thursday, May 17, 2018 1:45 PM | Comments (0)

SQL Server: How do I pull the ASCII value for each character in a column name?
This is a handy script to cycle through every character in a column to determine what each ascii value is. This is especially useful when a string match isn't matching. Often times, there is a hidden space, etc. DECLARE @counter int = 1;--DECLARE @asciiString varchar(10) = 'AA%#& '; DECLARE @asciiString varchar(100) SELECT @asciiString = [ColumnName] FROM schema.TableName where ColumnName like '%Something%'WHILE @counter <= DATALENGTH(@asciiString) BEGIN SELECT CHAR(ASCII(SUBSTRING(@ascii... ......

Posted On Tuesday, February 27, 2018 5:22 PM | Comments (1)

SQL Server: Why is it taking so long to take a database offline?
There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s). In the future, use this command:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

Posted On Friday, August 11, 2017 8:21 PM | Comments (0)

SQL Server: Why is it taking so long to take a database offline?
There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s). In the future, use this command:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

Posted On Friday, August 11, 2017 8:21 PM | Comments (0)

SQL Server: Alternative to Count(*) for VLDB
If you have a very large data base (VLDB) a select count(*) can take a long time to resolve. Try this statement as an alternative:USE YourDatabasename;SELECT s.name AS 'SchemaName' ,o.name AS 'TableName' ,SUM(p.row_count) AS 'RowCount'FROM sys.dm_db_partition_stats p JOIN sys.objects o ON o.object_id = p.object_id JOIN sys.schemas s ON o.schema_id = s.schema_idWHERE p.index_id < 2 AND o.type = 'U' AND s.name = 'Schema' AND o.name = 'TableName'GROUP BY s.name,o.nameORDER BY s.name,o.name;GO ......

Posted On Friday, December 4, 2015 8:26 AM | Comments (0)

SQL: Locate stored procs, views, etc. by text
This will locate specific text within an object on a given database. In this case, it will find all views, stored procs which have the text string "Department" somewhere in the body.use [databaseInstance]goSELECT DISTINCT o.name AS Object_Name, o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like '%Department%'; ......

Posted On Tuesday, December 1, 2015 12:24 PM | Comments (0)