Archive for the ‘MSSQL’ Category

Code to get SQL table and column schema from Entity Framework

  public static class Map { static Map() { var doc = new XmlDocument(); using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(“src.Admin.Entities.Model.msl”)) doc.Load(stream); //”urn:schemas-microsoft-com:windows:storage:mapping:CS” var nodes = doc.SelectNodes(“EntityTypeMapping”).Cast<XmlNode>(); LookupTableByType = nodes.ToDictionary( n => (NodeType)Enum.Parse(typeof(NodeType), n[“TypeName”].Value.Split(‘.’)[1], true), n => n.SelectSingleNode(@”MappingFragment/@StoreEntitySet’]”).Value ); Tables = nodes.ToDictionary( n => (NodeType)Enum.Parse(typeof(NodeType), n[“TypeName”].Value.Split(‘.’)[1], true), n => n.SelectNodes(“ScalarProperty”) .Cast<XmlNode>() .ToDictionary( s => s[“Name”].Value, s => […]

May 5, 2013 · admin · No Comments
Tags: , , ,  · Posted in: MSSQL, Tech

SQL to concatenate column values from multiple rows

  SELECT DISTINCT L2.Issue, substring( (SELECT ‘,’ + L1.Label AS [text()] FROM jira.label L1 WHERE L1.Issue = L2.Issue ORDER BY L1.Issue FOR XML PATH (”)) ,2 ,1000) [Labels]

March 27, 2013 · admin · No Comments
Posted in: MSSQL, Tech

SQL CTE to split a delimited string into a table

DECLARE @ListString varchar(8000) SET @ListString = ‘a,b,c,d’ DECLARE @List table (Item varchar(max)) ;with cte (item, list) as ( select cast(” as varchar(max)), @ListString union all select cast(left(list, charindex(‘,’,list+’,’)-1) as varchar(max)) item, stuff(list, 1, charindex(‘,’,list+’,’), ”) list from cte where list <> ” ) INSERT @List select item from cte where item <> ”

February 7, 2013 · admin · No Comments
Posted in: MSSQL, Tech

ClearTrace – Free SQL Server trace analyser

Download: ClearTrace Site: http://www.scalesql.com/cleartrace/

August 18, 2011 · admin · No Comments
Posted in: MSSQL, Tech

MSSQL TODAY Function

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) roughly twice as efficient as: CONVERT(DATETIME, CONVERT(varchar(10),@time, 120))

October 27, 2010 · admin · One Comment
Posted in: MSSQL

SET STATISTICS IO

Profiling the cost of each query: SET STATISTICS IO { ON | OFF } via SET STATISTICS IO. Also, http://msdn.microsoft.com/en-us/library/ff647793.aspx

October 24, 2010 · admin · No Comments
Posted in: MSSQL