SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

USE DatabaseName
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

Reference: Pinal Dave (http://www.SQLAuthority.com)

About these ads

263 thoughts on “SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

    • Hi

      I need to find a row to given value then read next value of row

      Example

      Row Width
      1 400
      2 500
      3 510
      4 682

      So I am find value 500 but I want display next row value 510

      How to find the next row value in Sql

      Please reply me , I am waiting for your mail..

      Regards

      Selva

  1. HI ,

    How can we get Row count for each column of a given table.

    where if the row count should say ,
    if Nulls are present in entire column for all rows should dispaly – NULL ,
    if the entire column is Blank for all rows it should show BLANK.

    Thanks.

  2. How can we find out the Column names in SQL Server

    like the ORACLE command

    DESC

    which is use to show the table structure

    thanks

    • By default there is no row_number
      However based on the order of the column you can find it using row_number() function from version 2005 onwards

      select row_number() over(order by somecol) ,* from your_table

  3. This SQL Query returns total numbers of column in table

    SELECT TOP 100 PERCENT WITH TIES c.TABLE_NAME , COUNT(*) NumColumns
    FROM INFORMATION_SCHEMA.[COLUMNS ] c
    inner join INFORMATION_SCHEMA.[TABLES] t
    ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.TABLE_NAME = T.TABLE_NAME
    GROUP BY c.TABLE_NAME
    ORDER BY c.TABLE_NAME

  4. Hi Champs ….

    Try this

    SELECT COUNT(*) Statment will return the row counts !!

    and one more thing with using SELECT COUNT(*) statment ….
    Because the SELECT COUNT(*) statement makes a full table scan to return the total table’s row count, it can take an extremely long time for large tables. There is another way to determine the total row count in a table. In this case, you can use the sysindexes system table. There is a ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):

    SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2

    Cheers
    Praveen

  5. Write a Query to select column value in row form

    Like
    Table

    Col1 Col2
    Aaa 1
    Bbb 2
    Ccc 3
    Ddd 4
    Eee 5

    Out put of query should belike
    Col1 Aaa Bbb Ccc Ddd Eee
    Col2 1 2 3 4 5

  6. hi vinod,
    try creating a udf. add a cursor to read each row in your table and concat the values as you read. output the values using the udf a select statement.
    hope this helps.

  7. hi suganya,
    you can write the query in this way
    select count(any column name) from yourtable.bcoz u can take any column to find the no of rows

  8. SQL Server 2000 & 2005 both support a stored procedure call sp_help

    call it with the tablename following it and you’ll get your table structure and indexes, filegroups, entities, etc….

    here are some examples of how to use it…

    sp_help MyTableName

    sp_msforeachtable “sp_help ‘?'”

    sp_msforeachtable “select ‘?’ TableName, count(*) as TotalRecords from ?”

  9. hi guys,
    how can i show some particular columns from a tabel without writing any column name?means even dnt know abt that particular column name.
    bhuvnesh

  10. How can we find out the Column names in SQL Server

    like the ORACLE command

    DESC

    which is use to show the table structure

    thanks

    To find a column name in a table..

    Use ”
    Go
    Select Column_Name from Information_Schema.Columns
    where Table_Name = ‘

    Note ** you can also use a wildcard
    Hopes this helps

    Wole

    • There are many methods

      Some of them are

      EXEC sp_columns ‘table name’

      SELECT column_name FROM information_schema.columns
      WHERE table_name=’tabl name’

      EXEC sp_help ‘table name’

  11. How to count indexes in particular table. i.e if we have 3 indexes in a particular table then how could we count in query. pls help

  12. HI Wole,

    Thanks for your query finding column names in a tabel using Information_Schema.Columns…

    i’m new to sql server…
    Thanks & Regds
    Rockin Benjamin.S

  13. MS SQL query of showing all tables whose name starts with “P” in specefic database, use this query.
    select * from sysobjects where name like ‘P%’

    But It will show Constraints and tables,
    if you want only user defined tables then use this query.

    select * from sysobjects where name like ‘P%’ and xtype = ‘u’

  14. Hi guys,
    This is another easy way to find the max row count in a table.

    select rowcnt from sysindexes where name in (
    select name from sysobjects where parent_obj in
    (
    select id from sysobjects where name = ‘Table_Name’
    )

  15. how can i count number of column in a row with same data?
    i have a table with column empid,month, day1, day2 ………….day31 i want to count number of ‘p’ and ‘a’ of that employee id?

      • i think you did not get the scenario correctly, here the requirement is something like an attendance sheet, where an empid is marked present (p) or absent (a) based on the days and @Deepshika want the number of days present in that month.

        take a table, table name : Attendance
        _____________________________________________
        | EmpID | Month | Day1| Day2| Day3|………………..|Day31|
        ————————————————————————
        |001 | Jan | P | A | P |………………..| P |
        |001 | Feb | A | P | p |……………….| |
        |002 | Sep | P | P | P |………………..| |
        |002 | Nov | A | A | P |………………..| |
        |_____________________________________________

        and so on,
        so as per @Deepshika requirements is to get the count of no of days present (P) or absent (A) for the empid 001 in the month of Jan and same for all the months and empid’s. here we need to use UNPIVOT, to the count of columns value.

        select EMPID, MONTH, DATA, Count(Data)
        (select EMPID, MONTH, COL, DATA from
        (select EMPID, MONTH, Day1, Day2, Day3,……………,Day31 from Attendance) as pvt UNPIVOT
        (DATA for COL in (Day1, Day2, Day3,……………,Day31) as UPVT)) as pt group by EMPID, MONTH, DATA

        i have given the sln i know, there may be many slns better than this one. if anyone know a better sln than provide that one here which will be helpful for other for developing the skills.

  16. Column count in a table

    create Procedure Prc_GetColumnCount
    @TableName varchar(20)
    as
    Begin
    Select Count(Name) from Syscolumns
    Where
    id =
    (Select id from SysObjects Where name = @TableName)
    End

    Prc_GetColumnCount ‘Table name’

  17. How do we extract all table list from SQL server?
    I am using toad for SQL server and trying to find some field.
    Can anyone pls tell me how to see the list of all the tables in SQL Server…………………

  18. What is the maximum number of records that SQL will return for a query? e.g. I have a db of 15,000 records and I want to run a query which should return 13,000. Will i see all 13,000 or some fewer number?
    Thanks

  19. Hi Frnds

    You can get list of all user defined table and no of rows from your sql server database with this query.

    It is quiet efficient and optimized.

    SELECT Rows, object_Name(id) as TableName FROM sysindexes WHERE indid < 2 and OBJECTPROPERTY(id, ‘IsUserTable’) = 1

    Cheers

    abhi :)

  20. Hi,
    In T SQL, how can we automate:
    – to copy all the columns of a table to make a new table
    – change the names of the new table and its columns using something like a prefix, which will make the difference of structures between the two tables.

    Thanks

  21. I guess u r looking something for this

    Use AdventureWorks;
    Select EmployeeID as ‘EmpNo’, LoginId as ‘LoginName’, BirthDate as ‘DOB’
    into MyNewTableName
    from HumanResources.Employee;
    Go;

    It creates new table and populates result set in that table

  22. @ Bimpa ,

    You can do two things, based on what you are looking at.

    1. If you just want a new table structure with out any constraints, keys, indexes…. just the columns and the table thats it, then try using…

    select * into new_table_name from old_table_name
    This will create the table and at the same time it will copy all data from source table to destination table, if you are using other database for destination table then use the complete notation, like servername.databasename.owner.object name.

    you can schedule this tsql as batch…

    2. You can try DTS or SSIS package to do the same. It will create a table on the destination, only table no constraints, no indexes … nothing only table with all columns, data types remaining same in both the cases.

    This task also creates a table and then inserts all the data from source to destination. you can schedule this package.

    3. if you want to have an exact table in the destination with all the features of source table, then you might want to work like this,

    create a batch file which will have all these things,

    1. script the source table and then run the script on destination, where you want to create the table, this will create a table with the same name with all the features of the source table.

    2. if you want to change the name, write another query,
    sp_rename object ‘oldname’ , ‘newname’

    3. Then final step, insert all the values from source to destination.

    insert into new_table_name select * from old_table_name.

    and schedule it … I hope it will work… I am not sure !

  23. 1. How to find out the table given a column, you only know that there is a table which has this column name, based on this information you can find out what table is it.

    use database name
    select table_name from information_schema.column where column_name = ‘column name’

    2. If you know the table name and you want to know what is the name of the column, there are many ways I am sure, one amoung them is,

    sp_help table_name … it will give alot of information… like owner of the table, all column name, their data type, primary keys , foriegn keys, indexes, constraint …. very useful information.

    3. You dont know what was the name of the table and what was the name of the column and you still want to search table and column,

    use database_name
    select table_name , column_name from information_schema.column — copy this code as it is, dont try to change the table_name or column _name

  24. How to find out user defined table names in the database,

    use database_name
    select * from sysobjects where type = ‘U’

    Similarly you can find out other objects created by user, simple change type =

    C = CHECK constraint

    D = Default or DEFAULT constraint

    F = FOREIGN KEY constraint

    L = Log

    FN = Scalar function

    IF = In-lined table-function

    P = Stored procedure

    PK = PRIMARY KEY constraint (type is K)

    RF = Replication filter stored procedure

    S = System table

    TF = Table function

    TR = Trigger

    U = User table ( this is the one I discussed above in the example)

    UQ = UNIQUE constraint (type is K)

    V = View

    X = Extended stored procedure

  25. Hi Pinal,

    Please answer my questions?

    1. When a database is restored to the original drive with the original filename, is the dbid changed?
    2. When a database is restored to a different drive with the original filename, is the dbid changed?

    Mani

  26. Hi Pinal,

    Here is the scenario:

    On Saturday morning, I created a login named abcd using Enterprise Manager (EM).
    In the database access tab, I gave abcd login access to databases x, y and z. I gave it db_datareader and db_datawriter roles in all three databases.
    When I looked in the databases, I saw abcd user had been created in each database with all the correct privileges.
    When I refreshed logins in EM, login abcd did not show that it had access to database z, but still had access to databases x and y.

    None of this makes any sense. EM knew the correct database when I created the login and granted access, but did not show it after I refreshed.

    When I did the same scenario on Monday morning, it worked properly and EM showed the correct database access.

    I want to know what happens?why not in sat but in Monday?

    Does SQL Server actually drop the database
    when the database is restored?

    Regards,
    Mani

  27. Each time I google advanced stuff for sql server I keep coming back here ; )

    Thanks , thanks , thanks !!!

    No one small idea partly made possible by this blog:

    Use the description field to pass info for the GUI generator and get rid of all hardcoded names in GUI ; )

    SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], value AS
    [DESCRIPTION]
    FROM sys.extended_properties AS ep
    INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
    INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
    = c.column_id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
    c.name = sc.column_name
    WHERE class = 1 and t.name = ‘TableName_tb’

    Could somebody tell me how to regex the [DESCRIPTION] = e.g. return only set of this one mathing the regex ?

  28. some of the tables have 0 rows in my database. I want to run a query to list out all the tables with 0 rows. how do i do it?

    i tried * from user_tables where num_rows is null

    but this query retrieves wrong data.

    Can anyone gimme the right query?

  29. Hi

    I want to find the perticular number from the column like
    ex —11658458784
    21565454797
    21564567897

    now i have to take 6 digit from the all number .

  30. Hi Dave,

    please reply back to my questions

    a. How to get rows of a column with out the column name.

    Thanks in anticipation

    bye.

  31. I have got here answer of my question that is “How can i count number of columns in a table”. Thanks Pinal.

  32. HI,

    I want to know the table structure using the quiery.

    Plz send me the quiery to know abt the table structure

  33. I found an alternative for ‘DESCRIBE tablename’ in mssql

    “SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’myTable';”

  34. I just want to read a specific row from a multi row return table…….i mean suppose……a query return this

    date
    ——-
    11/1/2007 3:19:28 PM
    12/1/2007 3:19:28 PM
    3/1/2008 3:19:28 PM
    2/1/2008 3:19:28 PM

    now…..how can i get an a specific row from above…..suppose i want to read each row but through numeric no…….if i say row no.3 or row no.2………..what should be the query……if anybody know plz help…….voidbutreturn@yahoo.com

  35. If I have a column whose every individual row value is known,
    how (by mssql query) can I calculate the row number of one particular value in that column?
    That is , what is row number of a value in a column?

    eg, a column ‘animal’ consists of 5 row values. cat,dog,man,bird,fly.

    the value ‘man’ belongs to 3rd row. But if I know only the value ‘man’ how can I calculate the corresponding row number , which is 3? (only by MSSQL query,using access 2003, and visual studio 2005)

  36. Hi,
    This is mustafa.

    I wrote one stored procedure.

    Inside the stored procedure I wrote one query it return mulitple set of record I want add all values that mean total what function I have use.

  37. Hi,

    Please bear with me as i am a newbie

    Is there any way to find out the no of rows for each month in a year for the last 10 years. so i should effectively get 120 rows (12months X10 years). I am able to create a stored procedure to retrive data based on month and year but cant i get this information at one shot?

    This is what i could come up with

    create procedure usp_emp_birth @month varchar(10),@year varchar(10)
    as
    (
    select @month as month ,@year as year, count(*)as [no of rows] from HumanResources.Employee
    where datename(month,birthdate)= @month and datepart(year,birthdate)= @year
    )

    exec usp_emp_birth may,1970

  38. my result set is:

    Name data

    abc new
    sss old
    abc current
    xxx jadu

    Now i requered show on follwing formate:

    abc new
    sss old
    current
    xxx jadu

    pls give me solution?

  39. How to get table list(table names) for a database according to batch size.

    e.g. if i there r 50 tables in database then if i want table names from 10 to 30 range.

    reqired in sql 2000 and oracle

  40. given the number of rows and columns how do we find the degree of Table?
    For example: What’s the degree of table having 10 cols and 1000 rows

  41. Hi,

    I have a table like:

    Table1
    (
    col1,
    col2,
    col3,
    )

    It has following sample data:

    col1 col2 col3
    1 2 3

    I want to run a query which provides the following output:

    COL1 1
    COL2 2
    COL3 3

    Can you please show me how to do this. I am using SQL Server 2005.

    Thanks,

    Amit

  42. Hi,

    Nice info, but i have started learning SQL now. Could you pls give some tips on how to go about this. I am a tester with 4+years of experience.

    Thanks
    Sachin

  43. Hi all,
    How to print the column name and that values in a row? I need to use that in one select query.

    (i.e)
    Dep.Code No.Of.Staff
    ——————————–
    101 25
    103 40
    Total 65

    Thanks,
    Karthik…:-)

  44. Hi all,

    I have a stored procedure which is used for insert/update of records in a table. A log is maintained for each operation performed by the user, in the application, and i need to insert the record into the log table only if successful transaction of insert/update is committed.
    For the log table, i need the column name of the row which is been updated in the query executed above.
    Please tell me how to get the column name which is been updated by the “Update” statement?
    Thanks in advance.

  45. Hi,

    How to return column names where the column contains a particular value. This will always be a single row – each column will contain either 1 or 0. I want to only return the columns that contain the 0 value.

    many thanks

  46. Why do these queries on the same table yield different results:

    –This statement runs very fast
    SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘mytable’) AND indid < 2

    –This one takes a while to execute
    select count(*) from mytable

    Mytable contains over 3millions records.

  47. I am writing a join. The join is producing more records than desired.

    Is there any way I can see the number of columns of the query returned when I say ‘select * from…’

    Number of columns of the query that was just executed, not of some table that is already there.

  48. Hi All,

    How to find the particular row in table?(means suppose one table contain 10 rows , in these 10 rows how to find the particular row(example in 10 rows i want 5 row how)?

  49. hi……

    select COUNT(*) from information_schema.columns
    where table_name = ‘Your_Table_Name

    its not working in oracle…………

  50. Hi, Ravi you can use following code to get the list of tables having 0 records.

    ———————————

    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
    WHERE a.row_count=0
    GROUP BY a.table_name, a.row_count, a.data_size
    DROP TABLE #temp
    —————————-

  51. Hi Sathish,

    if your are using SQL SERVER 2005, then you can use this following syntax

    SELECT *,
    COUNT(*) OVER(PARTITION BY ””) AS TotalRows
    FROM TbaleName

    Thanks,

    Tejas

  52. hi guys,

    i don’t usually use sql in my job…. but for this one purpose i’ve to use sqlite…

    so can somebody just tell me a simple query to count just the number of columns in a table…

    please hurry.. my time is running out…

    PS. this sql is not that bad as it seems :)

  53. @Sid

    There are many ways to get this done, here are some of these.

    select Count(*) ‘No Of columns’ from syscolumns where id = object_id(‘example’)

    or

    select count(*) ‘No Of columns’ from information_schema.columns where table_name = ‘example’

    Or

    select Count(*) ‘No Of columns’ from sys.columns where [object_id] = object_id(‘example’)

    Hope this helps,
    IM.

  54. Hi Guys
    New to SQL coding so can someone please help? I monitoring our replication and would like to take a row count of a table ‘table1’ and match it to the replication table on our tier 2 and 3 environment .
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table

    But I don’t want to receive and email everytime indication everything is ok. Ideally I would like to receive and email\ alert when the row counts do not match.

    The one table I am looking at sits on 5 different servers. Sorry bit stuck !!

    Also If I wanted to do row counts for mutiple tables accross the servers i.e table1 on all five servers, table 2, table 3 etc.

    How could like be done?

    Thanks in advance for your help!!

  55. thx Pinal :) It helps me today to detect that tbXml table in Wsus 3.0 with 160.000 records has 1GB size :OOO

    Makers of WSUS are crazy ;)))

    Thx anyway for simple and handy snippet.

    Regards

  56. Hi Pinal,

    Consider there are 5 databases in a sql server.Each database contains n number of tables. A column say ‘EMP id’ is repeated in different tables across the databases.

    My requirement is to write a single query which should return the count of that column in all the databases.

    Can u help me with this

  57. Hi All,

    Guys i need to create a reports in SSRs which gives me the comparsion on rowcount of table between 2 different database.

    2 different database has the same number of tables and with the same name.

    How can i get the rowcount of all the tables in both the database.

    Thank in advance
    Lalitha

  58. How would you do a query that returns the list of all of the column names, data types of each column and max size of each column (ie. varchar(50), varchar(100), etc…)

    Been stuck on this with no success ….

    Thank you !

  59. SELECT crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate
    get_field(crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate) col
    FROM (SELECT DISTINCT fpo,cdd,cdn,cfn,filedate
    FROM crime) crime where id=’MARSCARLCR952059′

    this query doesnt return the output,,plz correct it

  60. SELECT DISTINCT O.Name, I.Rows
    FROM SysObjects O WITH (NOLOCK) JOIN SysIndexes I WITH (NOLOCK) ON (O.ID = I.ID)
    WHERE O.xType = ‘U’
    AND I.Rows = (SELECT MAX(II.Rows)
    FROM SysObjects OO WITH (NOLOCK) JOIN SysIndexes II WITH (NOLOCK) ON (OO.ID = II.ID)
    WHERE OO.xType = ‘U’)

  61. Hi,

    I’m a novice to sql scripting. Am trying to figure out a design problem, involving some arithmatic computation.

    I have two tables temp1 and temp2 with two feilds account no., ( Common in both tables ) balance ( float data type ).

    1. I want to compare the balance feilds in temp1 and temp2. print out the no. of accounts and percentage of match and mismatch.
    2. output the a/c nos. whose balances dont match into a seperate table..

    another question, that I have is, how does sql handle the computation, if a value in a particular field is divided by 0 ?

    any help, will get me started.

    thanks

  62. Nice Article:

    >> Garima Wrote

    hi……

    select COUNT(*) from information_schema.columns
    where table_name = ‘Your_Table_Name

    its not working in oracle…………

    >> Reply to Garima

    I think dba_tab_columns will help you in achieving what you need.

    Assuming you are using 10g,
    sqlplus / as sysdba
    select count(*) from dba_tab_columns where table_name= ‘YOUR_TABLE_NAME';

    For example:
    SQL> show user;
    USER is “SYS”
    SQL> select count(*) from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT';

    COUNT(*)
    ———-
    8

    SQL> select column_name from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT';

    COLUMN_NAME
    ——————————
    EMPNO
    ENAME
    JOB
    MGR
    HIREDATE
    SAL
    COMM
    DEPTNO

    8 rows selected.

    SQL>

  63. Hi,

    Can any one help me in writing an SQL Query,

    in a database I having number of tables and out of them I want to have count of the tables that has ‘empno’ as one of their column.

    Please reply.
    Thanks & Regards,

  64. What if I have tables that are the same name but just different schemas. For instance in the database there was schemaA.TableA and schemaB.TableA. So TableA is listed two times in the same database but under different schemas. The spaceused for that query at the top wont work. Is there another query that will get all the tables and schemas space used?

  65. dear sir pinaldave.
    i have one doubt i have created a table dynamically using my .net project and it has not primary key what i did is i have altered the table i add a IndexID using

    alter table dbo._COMPANYDETAILS ADD IndexID int;

    this query
    but now what i need is the table has 5000 records i have to number it in the IndexID column from 1 to 5000 what is the query for it please reply me as soon as possible to my mail(ksnavinkumar@live.com) please sir…

  66. I find the question from OW very relevant and important. Matching tables by their names duplicate the records from tables with same names in different schemas. Can anybody please shed a light to this point?

  67. Here is my solution using catalog views, which are more flexible than using stored procedures, and considers tables with same name in different schemas:

    USE my_DB
    SELECT s.[name] [schema]
    , t.[name] [table]
    , t.create_date
    , t.modify_date
    , p.rows
    , SUM(a.used_pages) * 8 data_KB
    FROM sys.schemas s
    JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
    JOIN sys.partitions p
    ON t.[object_id] = p.[object_id]
    JOIN sys.allocation_units a
    ON a.container_id =p.partition_id
    GROUP BY
    s.[name]
    , t.[name]
    , t.create_date
    , t.modify_date
    , p.rows
    ORDER BY SUM(a.used_pages) DESC
    GO

    Note that the stored procedure sp_spaceused uses dynamic management view (sys.dm_db_partition_stats) instead of catalog view.

  68. All,
    Please can anyone help me out for the way out for writing a procedure for searching numbers in all tables in a database having multiple schemas.
    Hoping that you all champs can help me out as I am new bee.

    Rgds,
    BR..

  69. Hi All,

    In a table A, there are 3 columns and in 500 records like below

    Table A
    a1 a2 a3
    1 4 6
    2 5 7
    7 3 9
    etc……

    My Question is:
    How to write select statment do display 2,3,9 numbers records present in a table.

  70. I have a query which is belongs to compare the one table columns count in the diff database.and i need to find out if there is any diff between the database count need to display the no of count with coulmns.

    Any one can help to get the result.

  71. Hi guys,

    I have read thru this whole thread and all the solutions are great when it comes to reading table properties from the system.

    I still do not see a solution on how to get the information from a table generated from a select statement.

    Example:

    select * from tbl_1 join tbl_1 and tbl_2
    (two tables, one result set)

    I need the number of columns and all properties for this result set. remember this is a on the fly table not in the information_schema.

    Can it be done without converting it to a temp table?

  72. Thank you Brian,

    but I think you misunderstood my question. I am trying to display property information on a result set like it is done on an existing table:

    select *
    from information_schema.columns
    where table_catalog = ‘your_table’

      • I did give an example…

        select *
        from information_schema.columns
        where table_catalog = ‘your_table’

        where it says ‘your_table’ enter any one of your tables.

        Tha query will give you properties for that table (columns, keys, values, etc.)

        I want to pull the same properties from a result set that is not already a table.

        example:

        select col1, col2, col3 …
        from tbl1 join tbl1 on tbl2.c1 = tbl1.c1

        • @Dragonvic

          I don’t understand what you want to see. If the query result is a pseudo-table for this example, the properties of the returned COLUMNs would be same as the COLUMNs in the original TABLEs. Hence, INFORMATION_SCHEMA has it. IOW, select *
          from information_schema.columns
          where table_catalog IN (‘tbl1′, ‘tbl2′)

  73. That helps a bit, however, the actual tables (tbl1 and tbl2) are not native to sql, they are being imported using OPENROWSET, coming from a DBF and from an XLS. I need the ability in SQL to let me know how many columns these imports have so I can properly append to the correct local tables.

  74. No, that’s NOT what I was asking. I wnat to get the info from temp tables. But I think I know how, openrowset saves the data in a temp table.

    I know that you can pull information_schema from tempdb. The new challenge would be to hit the right table because tempdb saves the table name diferently from what you would name it.

  75. Hi Pinal

    how are you

    in my website i want to shoot auto mail after every day which

    content the total records of the table which enter in privious

    privious day can you please tell me how can i do this

    waiting to your reply its urgent

  76. Hello Dev,

    You can get the date of previous day by subtracting 1 from GETDATE() as below:
    SELECT GETDATE()-1

    You can write the WHERE clause as below:

    WHERE datecolumn >= CONVERT(VARCHAR(10), GETDATE()-1) AND datecolumn < CONVERT(VARCHAR(10), GETDATE())

    Regards,
    Pinal Dave

  77. hi Pinal,

    I am writing to the query given below

    select * from tblqcontacts
    WHERE txtDate = CONVERT(VARCHAR(10), GETDATE()-1)

    to find the previous date all records in a table

    but there is an error occurs which is

    “Server: Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”

    what i can do to find all record of previous date of current date

    waiting to your reply

  78. Hello Dev,

    The updated query is following:

    select * from tblqcontacts
    WHERE txtDate >= CONVERT(VARCHAR(10), GETDATE()-1,101) AND txtDate < CONVERT(VARCHAR(10), GETDATE(),101)

    Regards,
    Pinal Dave

  79. I have 10 rows with 3 columns in a table. i have to retrive the field values when column1 is x. In column1 many fields has value as x. Help me to form the SQL query

  80. can anybody solve this query, i have an employee table and i need to show all the data of those Employees who are Emp_Status ‘P’ (PERMANENT) and’B’ (PROBATIONARY)

  81. Hi Pinal

    i want to shoot a mail once in a day only through asp.net and

    sql can you please tell me whats the query for this

    waiting to your reply

    Dev

  82. Hello, -How i can search the special characters (char(10),char(13),…) in every table and field in a SQL Server Database ?

    Thanks, S.P.

  83. hi pinal,

    I have 1 table which contain DateTime, Enployee Number and usetype (used for IN or OUT) column, this is for time and attendance. Now I want to know how many employees are inside the campus and their employee ids. I am trying it with max(intime)>max(outtime) but i m not getting the desired result.
    sample data-

    emp no occurdatetime usetype (1 for IN,2
    for OUT)
    2099 2009-01-28 11:25:21.000 1
    3652 2009-01-28 11:26:12.000 1
    3607 2009-01-28 11:26:13.000 1
    6270 2009-01-28 11:26:39.000 2
    4489 2009-01-28 11:26:53.000 1
    6536 2009-01-28 11:27:00.000 1
    1742 2009-01-28 11:27:02.000 2
    2579 2009-01-28 11:27:09.000 1
    3853 2009-01-28 11:28:00.000 2
    5762 2009-01-28 11:29:09.000 1
    5668 2009-01-28 11:29:31.000 2
    4956 2009-01-28 11:31:43.000 2

    and how to convert this datetime data formate to Date and time separately.

    Please give me some clue to move further.

    thanks

  84. i am working on SQL. i want to set a counter for primary key which has some data input, so later when i add other rows the primary kry should increase automatically.. Primary key is “Prot1″
    Nxt should be “Prot2″

  85. Hi there!,

    Here you can find the sql command to get number of COLUMNS in a table….

    select TABLE_NAME, count(*) COLUMNS1 from all_tab_columns where owner=’EMP’
    GROUP BY TABLE_NAME
    ORDER BY COLUMNS1
    /

    • Hi,

      I used the below queries to fetch the number of columns in a temp table.

      select count(*) from information_schema.columns
      where table_name = ‘#temptable’

      select count(*) Noofcolumns from SYSCOLUMNS
      where id=(select id from SYSOBJECTS where name=’#temptable’)

      both the above queries didn’t give the desired result.

      Please tell me how to retrieve the number of columns in a temp table.

      Thanks in advance,
      Madan

  86. Im having some errors here:

    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

    What names should i use? i dont understand this part…

    Thanks for reading!

  87. I have two table a where the coulmn is date and userid and another table emp_info contain userid,name dept now I want to find the information from emp_info who is absent in table a between two date where I may assign the holiday.

    Please help me

  88. Hi Pinal,

    I am having a strange problem when assigning foreign key to a table.
    the error message no.1767: foreign key references invalid table table1.
    error.1750:Could not create constraint. See previous errors.

    although the table table1 is already created with the primary key which the foreign key is referencing.
    Please help me out.

  89. in SSRS to count rownumber

    =Runningvalue(a.value, count, “Dataset1″)

    or

    =runningvalue(a.value, countdistrict, “Datasetname”)

    or

    =runningvalue(a.value, sum, “groupname”)

      • —Try for Pubs database which has default schema name dbo or make a table with default schema else you need to add parameter for procedure for schema.

        CREATE procedure TopNcolumns
        (
        @tableName varchar(100),
        @n int

        )
        as
        Declare @s varchar(2000)
        set @s=”
        If @n>0
        Begin

        DECLARE @column_n varchar(30)
        declare @temp int
        set @temp=1
        DECLARE @getcolumn_n CURSOR
        SET @getcolumn_n = CURSOR FOR
        SELECT column_name
        FROM information_schema.columns where table_name=@tableName order by
        ordinal_position
        OPEN @getcolumn_n
        FETCH NEXT
        FROM @getcolumn_n INTO @column_n
        WHILE @temp<=@n
        BEGIN
        set @temp=@temp+1
        set @s=@s+','+ @column_n
        PRINT @column_n
        FETCH NEXT
        FROM @getcolumn_n INTO @column_n
        END
        CLOSE @getcolumn_n
        DEALLOCATE @getcolumn_n

        Set @s=substring(@s,2,len(@s)-1)
        Exec ('Select '+@s+' from '+@tablename)
        End
        Else
        Print ' 0 clumn passed as parameter'

        —-now try this…….
        exec TopNcolumns 'jobs' ,3

  90. Hi all

    when i export to csv file from ssrs2008 one extra row is displaying header, how to hide that particular row ?????????

    Thank

  91. SELECT sysobjects.Name [TableName], sysindexes.Rows
    FROM sysobjects
    JOIN sysindexes ON sysobjects.id = sysindexes.id
    WHERE type = ‘U’ AND sysindexes.IndId < 2
    ORDER BY 1,2

  92. How to get number of columns from select query :
    Example:

    select xx, yy from trade where ….

    number of columns is 2 in this case, is there a way to find out dynamically?? Please help

  93. Query to find table size for all tables of each database in a server:

    DECLARE @DatabaseName VARCHAR(100)
    DECLARE @SQL VARCHAR(max)

    –Main table to keep all data
    CREATE TABLE #TempTableMain
    (
    databaseName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
    )

    –table to keep data per database and then truncate for the next database
    CREATE TABLE #TempTable
    (
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
    )

    –table to store tablenames in a particular database
    create table #tableName (name varchar(100))

    –Cursor for running query per database
    DECLARE databaseCursor CURSOR
    FOR select [name] from msdb.sys.databases
    where [name] not in (‘master’,’tempdb’,’model’,’msdb’)

    OPEN databaseCursor
    FETCH NEXT FROM databaseCursor INTO @DatabaseName
    WHILE (@@Fetch_Status >= 0)
    BEGIN
    –storing tableNames of a particuler database in a temp table
    SELECT @SQL = ‘use ‘+@DatabaseName+’
    insert into #tableName
    select [name] from sys.tables’
    exec(@SQL)

    –soring table information in temp table
    SELECT @SQL =
    COALESCE(@SQL + CHAR(13) + ‘ ‘ ,”) +

    use ‘+ @DatabaseName+’
    INSERT #TempTable
    EXEC sp_spaceused ‘+name
    from #tableName
    print @SQL
    exec (@SQL)

    –storing data for the current database in the main table
    INSERT #TempTableMain
    select @DatabaseName,* from #TempTable

    –truncating temp tables for next run
    truncate table #tableName
    truncate table #TempTable

    FETCH NEXT FROM databaseCursor INTO @DatabaseName
    END
    CLOSE databaseCursor
    DEALLOCATE databaseCursor

    –tables to get size in MB and GB
    –this part is optional and configurable as per user and usage
    CREATE TABLE #TempTableMainMB
    (
    databaseName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSizeMB varchar(50),
    dataSizeMB varchar(50),
    indexSizeMB varchar(50),
    unusedSizeMB varchar(50)
    )

    CREATE TABLE #TempTableMainGB
    (
    databaseName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSizeGB varchar(50),
    dataSizeGB varchar(50),
    indexSizeGB varchar(50),
    unusedSizeGB varchar(50)
    )

    insert #TempTableMainMB
    select * from #TempTableMain

    –Remove ‘KB’ from tables
    update #TempTableMainMB
    set reservedSizeMB=REPLACE(reservedSizeMB,’ KB’,”),
    dataSizeMB=REPLACE(dataSizeMB,’ KB’,”),
    indexSizeMB=REPLACE(indexSizeMB,’ KB’,”),
    unusedSizeMB=REPLACE(unusedSizeMB,’ KB’,”)

    update #TempTableMainMB
    set reservedSizeMB=cast(CAST(reservedSizeMB as INT)/1024 AS varchar(100)),
    dataSizeMB=cast(CAST(dataSizeMB as INT)/1024 AS varchar(100)),
    indexSizeMB=cast(CAST(indexSizeMB as INT)/1024 AS varchar(100)),
    unusedSizeMB=cast(CAST(unusedSizeMB as INT)/1024 AS varchar(100))

    insert #TempTableMainGB
    select * from #TempTableMainMB

    update #TempTableMainGB
    set reservedSizeGB=cast(CAST(reservedSizeGB as INT)/1024 AS varchar(100)),
    dataSizeGB=cast(CAST(dataSizeGB as INT)/1024 AS varchar(100)),
    indexSizeGB=cast(CAST(indexSizeGB as INT)/1024 AS varchar(100)),
    unusedSizeGB=cast(CAST(unusedSizeGB as INT)/1024 AS varchar(100))

    –display of results
    SELECT * FROM #TempTableMain order by 1
    SELECT * FROM #TempTableMainMB ORDER BY 1
    SELECT * FROM #TempTableMainGB ORDER BY 1

    –Final cleanup
    DROP TABLE #TempTable
    DROP TABLE #TempTableMain
    DROP TABLE #TempTableMainMB
    DROP TABLE #TempTableMainGB
    drop table #tableName

  94. How to Display Table’s column as row
    Ex: I Have a Table Employee
    Emp_id Emp_Name
    101 Santosh
    102 Vijay
    103 Anilkumar

    Now i wanna to display all records of Emp_Name field as horizantally. Like:

    Santosh Vijay Anilkumar ………………

    Please Help me
    Thanks
    Santos S.Pawar

  95. hi,

    how to find the table estimate size based on the number of rows as input. If I give no of rows per a table, it should give estimated size of that table.

  96. No of columns in a table can be returned using the below query:
    Select count(*) from user_tab_columns where table_name = ‘tablename';

  97. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  98. CREATE FUNCTION FN_GET_AGE_YMD
    (
    @IP_DOB DATETIME,
    @IP_TILLDATE DATETIME
    )
    RETURNS TABLE
    AS
    RETURN(
    SELECT YEARS,MONTHS,DAYS,DATEDIFF(HOUR,CURDATE,@IP_TILLDATE)-24*DAYS AS HOURS, 0 AS MINUTES, 0 AS SECONDS
    FROM(
    SELECT *,
    CASE WHEN DATEPART(HOUR,CURDATE)>DATEPART(HOUR,@IP_TILLDATE)
    THEN DATEDIFF(DAY,CURDATE,@IP_TILLDATE)-1
    ELSE DATEDIFF(DAY,CURDATE,@IP_TILLDATE) END DAYS
    FROM(
    SELECT *,DATEADD(MONTH,MONTHS,CURYEAR)CURDATE
    FROM(
    SELECT *,CASE WHEN DAY(@IP_DOB)>DAY(@IP_TILLDATE) OR
    (DAY(@IP_DOB)=DAY(@IP_TILLDATE)
    AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
    THEN DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE)-1
    ELSE DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE) END MONTHS
    FROM(
    SELECT *,DATEADD(YEAR,YEARS,@IP_DOB)CURYEAR
    FROM(
    SELECT CASE WHEN MONTH(@IP_DOB)>MONTH(@IP_TILLDATE) OR
    (MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
    AND DAY(@IP_DOB)>DAY(@IP_TILLDATE)) OR
    (MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
    AND DAY(@IP_DOB)=DAY(@IP_TILLDATE)
    AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
    THEN DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE)-1
    ELSE DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE) END YEARS
    )A)B)C)D)E)

  99. hi all,
    Can u pls tell me the query to get all the values from the table .. ie.. for example if i give select * from agent(table name)… it should return me all the values including the column header …it should return me as table to my front end.. the table name will be given by the user.. so there should not be any hardcode of values in the code… I wanna the query to be used in my coding

  100. Hi,

    This is also a very good article as usual we got every article here.
    As Discussion going here..
    my Query is something deeper in this.. I want size of all the tables as group of months..
    I want size of all table month wise..
    Like I want to know what was the size of my table in january and what was in July so i can compare the increasing rate of my tables..
    I want output like that contains month and size of table in that month and table name..

    Please Help me…

  101. Hi am amar i am a SQL developer today i got one of the requirement ,Soupose I have 100 tables for each table there is a column mame called ‘status ‘.i want to retrive status column of all the tables.Can any one help me .regarding this query.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s