# TSQL Code

### Question

• Hi everybody,

i am trying to solve this problem?can anyone could help me to get out of this problem?

1) What is a simple way to find structure and size of the multiple
tables in one database, such as names, number of columns, number of rows in

one statement?****

2) Imagine a table which contains records for all weekly number of bugs
discovered by each QA analysts in the team for one year. Please describe a
simple SQL statement for identifying a winner (an employee who found the
biggest number of bugs during that year)  by writing one statement?****
Saturday, July 07, 2012 3:33 AM

### All replies

• Hi every body,

can anybody help me to solve this questions?

1) What is a simple way to find structure and size of the multiple

tables in one database, such as names, number of columns, number of rows in

one statement?****

2))     Imagine a table which contains records for all weekly number of bugs
discovered by each QA analysts in the team for one year. Please describe a
simple SQL statement for identifying a winner (an employee who found the
biggest number of bugs during that year)  by writing one statement?****
Saturday, July 07, 2012 3:28 AM
• Ans 1 )

```SELECT s.name as tablename, st.row_count,count(sc.name)
FROM sys.dm_db_partition_stats st
inner join sys.tables s ON st.OBJECT_ID = s.OBJECT_ID
INNER JOIN sys.columns sc ON s.object_id = sc.object_id
WHERE index_id < 2
group by s.name,st.row_count
ORDER BY st.row_count DESC```

Ans 2 )

```declare @table table(ID INT,BugDate datetime)

insert into @table
values(1,'2012-01-01'),
(2,'2012-01-01'),
(3,'2012-01-01'),
(1,'2012-02-02'),
(1,'2012-03-03'),
(2,'2012-03-03'),
(3,'2012-03-04'),
(2,'2011-01-01'),
(3,'2011-01-01'),
(1,'2011-01-01'),
(2,'2011-01-01'),
(3,'2011-01-01'),
(1,'2012-01-01'),
(1,'2012-01-01'),
(3,'2011-01-01')

;with cte as
(	select *,Year(bugdate) as year,ROW_NUMBER() over (partition by ID,Year(bugdate) order by year(bugdate)) as rowID
from @table
)

select ID,mxroID,c.year from
cte c
INNER JOIN
(
select MAX(rowID) as mxroID,year from cte
group by year
) c1 ON c.rowID = c1.mxroID
AND c.year = c1.year

```

Saturday, July 07, 2012 4:49 AM
• Hi,

1) For all table sizes and row count have a look at this good post

- Chintak (My Blog)

Saturday, July 07, 2012 6:55 AM
•  1)

```SELECT
S.name AS SchemaName,
T.name AS TableName,
C.name AS ColumnName,
D.name AS DataTypeName,
* -- All the columns you can make use from
FROM sys.Schemas S
INNER JOIN sys.Tables T
ON S.schema_id = T.schema_id
INNER JOIN sys.sysindexes I
ON T.object_id = i.id
INNER JOIN sys.Columns C
ON T.object_id = C.object_id
INNER JOIN sys.Types D
ON C.system_type_id = D.system_type_id
WHERE
D.system_type_id = D.user_type_id AND
I.indid IN (0,1)```

2) - Mock Up Query

```SELECT
TOP 1 WITH TIES
[QAAnalyst],
DATEPART(year, [DiscoveredDate]) [Year of Discovery],
SUM([NumberOfBugs]) [TotalNumberOfBugs]
FROM [SomeSchema].[TableOfInterest]
WHERE
DATEPART(year, [DiscoveredDate]) = 2012
GROUP BY
[QAAnalyst],
DATEPART(year, [DiscoveredDate])
ORDER BY
[TotalNumberOfBugs] DESC```

Microsoft Certified Trainer & MVP on SQL Server

Saturday, July 07, 2012 11:54 AM
• try this to get details of tables for all databases avillable :-

declare csr_db cursor for
select [name] from master..sysdatabases
where [dbid] >4
declare @cmd varchar(max)
declare @dbs varchar(256)
open csr_db
fetch csr_db into @dbs
while @@fetch_status = 0
BEGIN

set @cmd = 'Use ['+@dbs+']
CREATE TABLE #Tablespace
(
TableName varchar(500),
RowCounts int,
TableSize varchar(50),
DataSize varchar(50),
IndexSize varchar(50),
Unused varchar(50)
)

INSERT INTO #Tablespace Exec sp_MSforeachtable ''sp_spaceused "?" ''

SELECT db_name(),[Table],convert(varchar(100),RowCnt),Replace(TableSize,'' KB'','''') "TableSize", getdate() "Dtime" FROM ( SELECT [Table], Max(rows) "RowCnt" FROM (SELECT OBJECT_NAME(OBJECT_ID) AS [Table], *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( DB_Name()) and OBJECT_NAME(OBJECT_ID) in (Select [name] from sys.tables)) T
INNER JOIN
sysindexes idx
ON T.object_id = idx.id
Group By [Table]) T2
INNER JOIN
#Tablespace
ON TableName = [Table]
Order by RowCnt Desc

drop table #Tablespace'

exec(@cmd)

fetch csr_db into @dbs
END
close csr_db
deallocate csr_db

Saturday, July 07, 2012 12:44 PM
• Thanks Chintak,i will go through that site.
Saturday, July 07, 2012 12:52 PM
• Hi Mattias,

Thank you very much for your help

Saturday, July 07, 2012 12:57 PM
• Hi RohitGarg,

Thank you very much for your detail explanation,really appreciate

Saturday, July 07, 2012 12:59 PM
• Great, I'm happy it helped. And if you feel my submission it's nice if you propose it as an answer.

You just target database by adding a USE {databasename}

Microsoft Certified Trainer & MVP on SQL Server

Saturday, July 07, 2012 1:01 PM
• 1. Take a look at this blog

# How to get information about all databases without a loop

2. Select top (1) Employee, count(*) as BugsCount from Bugs GROUP BY Employee order by COUNT(*) DESC

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Sunday, July 08, 2012 3:45 AM