• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

upgrade query to 2008

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(i.id) = OBJECT_NAME(c.object_id) and
 i.indid in (0,1) and
 i.rows <>0 and
 LEN(i.id)>2 where
c.name = 'salesno'
0
25112
Asked:
25112
2 Solutions
 
AlokJain0412Commented:
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 sobjects.name ,* 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
Cheers
0
 
TempDBACommented:
25112,
      Here you go...


SELECT TableName,
       NumRows,
       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,
                  SUM(
                      CASE
                           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
                      END
                  ) 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
           WHERE
                    t.[type] = 'U' AND
                          t.is_ms_shipped <> 1
         
           GROUP BY
                  t.[name]
       ) AS subselect
WHERE
         NumRows > 0      
ORDER BY
      (reservedpages * 8192 / 1024) DESC
0
 
25112Author Commented:
got it..
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now