none
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

Answers

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
    
    


    Please vote if you find this posting was helpful or Mark it as answered.


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

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

    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/t-sql-tuesday-31-logging

    2) For Second, Can you please add some schema of your table.


    - 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
    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    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
    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    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