Archive

Posts Tagged ‘MSSQL’

Checking the table size

June 29th, 2010
78 views No comments

I was reading a bit about MSSQL and stumbled on this page. It’s a nice looking script to get the table size and you never know when it might come in handy :)

If you have your own tricks or tips, let us know!

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘BASE TABLE’ and TABLE_NAME not like ‘dt%’ order by TABLE_NAME asc

declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END

update @Space set data = replace(data, ‘ KB’, ”)
update @Space set data = convert(int, data)/1000
update @Space set data = data + ‘ MB’
update @Space set reserved = replace(reserved, ‘ KB’, ”)
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ‘ MB’

select * from @Space order by convert(int, replace(data, ‘ MB’, ”)) desc

Arkie Everything else ,

Limit function in MSSQL 2000

November 28th, 2009
310 views No comments

Since there is no real limit functionality in MSSQL 2000 , getting a selection of records can be somewhat difficult. Well, here’s a solution to get a number of records that works like the “limit” function that can be found in MsSQL

SELECT col FROM (
   SELECT top 50 * FROM (
      SELECT top 200 * FROM tbl1
      WHERE col > 1 ) ORDER BY col
   ) AS newtbl1 ORDER BY coldesc
) AS newtbl2 ORDER BY col ASC

Note that there is a similiar limit function in MSSQL 2k5 called “rownumber” which makes returning the desired results a lot easier. ;/

Arkie Code and Stuff , , , , , ,

[MSSQL] Updating a column with values from another column

September 22nd, 2009
155 views No comments
UPDATE Table1
SET columnToUpdate = columnToUpdateFrom

Arkie Code and Stuff

[MSSQL] Check if a value in a column is numeric

September 21st, 2009
189 views No comments

To show numeric values from a column only you can do:

SELECT column1 FROM table1
WHERE isnumeric(column1) = 1

Or if you want to show non numeric values from a column you can do:

SELECT column1 FROM table1
WHERE isnumeric(column1) = 0

Arkie Everything else