Sunday, May 30, 2010

SQL Query to List number of rows in all tables in a Database

Hey...

I was googling to find a query to give me the number of rows in all tables in a database. This was helpful to cross verify whether all records are moved to the hosting Database from my local database.

Resource: www.SQLAuthority.com

Syntax:

GO

CREATE TABLE #temp (

table_name sysname ,

row_count INT,

reserved_size VARCHAR(50),

data_size VARCHAR(50),

index_size VARCHAR(50),

unused_size VARCHAR(50))

SET NOCOUNT ON

INSERT #temp

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,

a.row_count,

COUNT(*) AS col_count,

a.data_size

FROM #temp a

INNER JOIN information_schema.columns b

ON a.table_name collate database_default

= b.table_name collate database_default

GROUP BY a.table_name, a.row_count, a.data_size

ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #temp

Hope it helpz
Fauzi

Sunday, May 23, 2010

SQL Stored Procedure to search for a word in Database tables

Was in Need of the same...

SQL Script:

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

--EXEC SearchAllTables 'Fauzi'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

END

END

SELECT ColumnName, ColumnValue FROM #Results

END

--------------------------------------------------------------------------------------

To Test : EXEC SearchAllTables 'Some Text'


Thanks to http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Hope it helps

Fauzi

SQL Query to List tables in a Database based on their size occupied

Hi...


This time I was looking forward to list the tables in the database based on their size...


QUERY:



WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )

AS (SELECT

s.Name

, o.Name

, p.used_page_count * 8

, p.reserved_page_count * 8

, p.row_count

, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end

FROM sys.dm_db_partition_stats p

INNER JOIN sys.objects as o ON o.object_id = p.object_id

INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id

LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id

WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)

SELECT t.schema_name

, t.table_name

, sum(t.used) as used_in_kb

, sum(t.reserved) as reserved_in_kb

,sum(t.tbl_rows) as rows

FROM table_space_usage as t

GROUP BY t.schema_name , t.table_name

ORDER BY used_in_kb desc


Hope It Helps

Fauzi ~ 4 Z




SQL Query to List all tables with their Row counts in a Database

Hi there...

I was in need to analyze a database. This SQL Query to List all tables with their Row counts in a Database was pretty much useful.

SELECT

[TableName] = so.name,

[RowCount] = MAX(si.rows)

FROM

sysobjects so,

sysindexes si

WHERE

so.xtype = 'U'

AND

si.id = OBJECT_ID(so.name)

GROUP BY

so.name

ORDER BY

2 DESC

Hope it helpz
Fauzi