Category Archives: SQL Server 2005

TSQL IPAddrInt, IpAddrStr

USE [WAN] GO /****** Object:  UserDefinedFunction [dbo].[IPAddrInt]    Script Date: 08/29/2011 20:02:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[IPAddrInt] (@IP AS VARCHAR(15)) RETURNS BIGINT AS BEGIN RETURN (CONVERT(BIGINT, PARSENAME(@IP,1)) + CONVERT(BIGINT, PARSENAME(@IP,2)) * 256 + CONVERT(BIGINT, … Continue reading

Posted in SQL Server 2005 | Leave a comment

syscomments

syscomments SELECT –c.id, c.text o.id,number,refdate,xtype,name,text FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id

Posted in SQL Server 2005 | Leave a comment

TSQL – table row count using sysindexes or DMV

Row Counts Using sysindexes  (SQL 2000) — Shows all user tables and row counts for the current database — Remove OBJECTPROPERTY function call to include system objects SELECT o.NAME, i.rowcnt FROM sysindexes AS i INNER JOIN sysobjects AS o ON i.id … Continue reading

Posted in SQL Server 2005 | Leave a comment

TSQL – Loop through records without using a cursor

declare @devip varchar(15) select distinct devip into #1 from table1 set @devip=” while @devip is not null begin    select @devip=min(devip) from #1 where devip>@devip    print @devip    — do something with @devip end drop table #1

Posted in SQL Server 2005 | Leave a comment

Storing Images and BLOB files in SQL Server

CREATE TABLE BLOBTest ( TestID int IDENTITY(1,1), BLOBName varChar(50), BLOBData varBinary(MAX) ); INSERT INTO BLOBTest (BLOBName, BLOBData) SELECT ‘First test file’, BulkColumn FROM OPENROWSET( Bulk ‘C:\temp\nextup.jpg’, SINGLE_BLOB) AS BLOB Storing Images and BLOB files in SQL Server (databasejournal.com) Part 2

Posted in SQL Server 2005 | Leave a comment

Displaying sizes of SQL Server Database Tables

CREATE PROCEDURE dbo.TableSpaceUsed AS — Create the temporary table… CREATE TABLE #tblResults ( [name] nvarchar(20), [rows] int, [reserved] varchar(18), [reserved_int] int default(0), [data] varchar(18), [data_int] int default(0), [index_size] varchar(18), [index_size_int] int default(0), [unused] varchar(18), [unused_int] int default(0) ) — Populate … Continue reading

Posted in SQL Server 2005 | 1 Comment

SQLSERVER – OPENROWSET / MSDASQL to read CSV/TSV files

The following will read a csv file. SELECT * FROM OPENROWSET (‘MSDASQL’, ‘Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\data;’, ‘SELECT * from test.csv’); The following will read a tsv file (with a txt extension). SELECT * FROM OPENROWSET (‘MSDASQL’, ‘Driver={Microsoft Text Driver … Continue reading

Posted in SQL Server 2005 | Leave a comment