upgrade query to 2008

Posted on 2011-10-08
Last Modified: 2012-06-22
can you upgrade this to 2008, please?

and is this query over all efficient? to see all user tables with data?

select OBJECT_NAME(id), rows from sysindexes i join sys.columns c
on OBJECT_NAME( = OBJECT_NAME(c.object_id) and
 i.indid in (0,1) and
 i.rows <>0 and
 LEN(>2 where = 'salesno'
Question by:25112
    LVL 5

    Assisted Solution

    HI we can retrive meta information of database with such type of tables
    If u want to get the all information of user tables resides in your database write following
    SELECT ,* FROM sysobjects sobjects WHERE sobjects.xtype = 'U'

    Here is a list of other object types you can search for as well:

    C: Check constraint
    D: Default constraint
    F: Foreign Key constraint
    L: Log
    P: Stored procedure
    PK: Primary Key constraint
    RF: Replication Filter stored procedure
    S: System table
    TR: Trigger
    U: User table
    UQ: Unique constraint
    V: View
    X: Extended stored procedure

    With this reference link contains query will help you to all type of Meta information
    LVL 25

    Accepted Solution

          Here you go...

    SELECT TableName,
           reservedpages * 8192 / 1024 AS TotalSpace_In_KB,
           pages * 8192 / 1024 AS DataSpace_In_KB,
           (usedpages -pages) * 8192 / 1024 AS IndexSpace_In_KB,
           (reservedpages -usedpages) * 8192 / 1024 AS UnusedSpace_In_KB
    FROM   (
               SELECT t.[name] AS tablename,
                      AVG([rows]) AS NumRows,
                      SUM(total_pages) AS reservedpages,
                      SUM(used_pages) AS usedpages,
                               WHEN it.internal_type IN (202, 204) THEN 0
                               WHEN a.type <> 1 THEN a.used_pages
                               WHEN p.index_id < 2 THEN a.data_pages
                               ELSE 0
                      ) AS pages
               FROM   sys.allocation_units AS a
                      JOIN sys.partitions AS p
                           ON  p.partition_id = a.container_id
                      LEFT JOIN sys.internal_tables it
                           ON  p.object_id = it.object_id
                      JOIN sys.tables AS t
                           ON  p.object_id = t.object_id
                        t.[type] = 'U' AND
                              t.is_ms_shipped <> 1
               GROUP BY
           ) AS subselect
             NumRows > 0      
          (reservedpages * 8192 / 1024) DESC
    LVL 5

    Author Comment

    got it..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now