Category: Microsoft SQL Server

Posted on: November 8, 2011 Posted by: John Huh Comments: 0

Dump database structure

How do you get a full database structure from Microsoft SQL Server? [sql] select * from INFORMATION_SCHEMA.COLUMNS [/sql]

Posted on: February 17, 2011 Posted by: John Huh Comments: 0

Find first and last sunday

This is a nifty query that I can use on reporting, I’m sure there are other uses for it. [sql] declare @year int set @year =2011 — First and Last Sunday by SqlServerCurry.com select min(dates) as first_sunday,max(dates) as last_sunday from ( select dateadd(day,number-1,DATEADD(year,@year-1900,0)) as dates from master..spt_values where type=’p’ and …

Posted on: November 12, 2010 Posted by: John Huh Comments: 0

Text what?

Problem with datatype “text” is… you can’t search and replace a string using the REPLACE function. One way is to cast the text field to a varchar/nvarchar to use the REPLACE function. Example: [sourcecode language=”sql”] UPDATE tablename SET textfield = REPLACE( CAST(textfield AS VARCHAR(8000)), ‘\r\n’, ” ) [/sourcecode] Also, len(textfield) …

Posted on: September 28, 2010 Posted by: John Huh Comments: 0

Limit what?

How do you start at a certain row and limit it? Used for paging perhaps, but here it is how: [sourcecode language=”sql”] SELECT Description, Date FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, Description, Date FROM LOG) AS LogWithRowNumbers WHERE Row >= 11 AND Row <= 20 [/sourcecode]

Posted on: September 22, 2010 Posted by: John Huh Comments: 1

Microsoft SQL Xtype?

XType Datatype —————- 34 image 35 text 36 uniqueidentifier 48 tinyint 52 smallint 56 int 58 smalldatetime 59 real 60 money 61 datetime 62 float 98 sql_variant 99 ntext 104 bit 106 decimal 108 numeric 122 smallmoney 127 bigint 165 varbinary 167 varchar 173 binary 175 char 189 timestamp 231 …

Posted on: September 22, 2010 Posted by: John Huh Comments: 0

Tables and Fields dump

Here’s a quick way to see the user tables and fields within a database. [sourcecode language=”sql”] select s.id, s.name as table_name, c.[name] as column_name, t.name as datatype, c.length, c.xprec from sysobjects s inner join syscolumns c on c.id = s.id inner join systypes t on t.xtype = c.xtype where s.type …