Loading ...
Sorry, an error occurred while loading the content.
 

RE: MUGH-SQLCON No of Tables in a database

Expand Messages
  • Inderjeet Chana
    what is the use of this script ...simple run this statement use select count(*) from information_schema.tables where table_type= Base Table go
    Message 1 of 6 , Aug 1, 2007
      what is the use of this script ...simple run this
      statement

      use <database>

      select count(*) from information_schema.tables where
      table_type='Base Table'
      go
      select table_name from information_schema.tables where
      table_type='Base Table'

      Inder

      --- Sunil Kak <sunilkak@...> wrote:

      >
      >
      >
      >
      > Below is the script
      >
      >
      >
      > ******
      >
      >
      >
      > DECLARE @table_name VARCHAR(500)
      >
      > DECLARE @schema_name VARCHAR(500)
      >
      > DECLARE @tab1 TABLE(
      >
      > tablename VARCHAR (500) collate
      > database_default
      >
      > ,schemaname VARCHAR(500) collate
      > database_default
      >
      > )
      >
      >
      >
      > CREATE TABLE #temp_Table (
      >
      > tablename sysname
      >
      > ,row_count INT
      >
      > ,reserved VARCHAR(50) collate
      > database_default
      >
      > ,data VARCHAR(50) collate database_default
      >
      > ,index_size VARCHAR(50) collate
      > database_default
      >
      > ,unused VARCHAR(50) collate database_default
      >
      >
      > )
      >
      >
      >
      > INSERT INTO @tab1
      >
      > SELECT Table_Name, Table_Schema
      >
      > FROM information_schema.tables
      >
      > WHERE TABLE_TYPE = 'BASE TABLE'
      >
      >
      >
      > DECLARE c1 CURSOR FOR
      >
      > SELECT Table_Schema + '.' + Table_Name
      >
      > FROM information_schema.tables t1
      >
      > WHERE TABLE_TYPE = 'BASE TABLE'
      >
      >
      >
      > OPEN c1
      >
      > FETCH NEXT FROM c1 INTO @table_name
      >
      > WHILE @@FETCH_STATUS = 0
      >
      > BEGIN
      >
      > SET @table_name = REPLACE(@table_name,
      > '[','');
      >
      > SET @table_name = REPLACE(@table_name,
      > ']','');
      >
      >
      >
      > -- make sure the object exists before
      > calling sp_spacedused
      >
      > IF EXISTS(SELECT id FROM sysobjects WHERE id
      > =
      > OBJECT_ID(@table_name))
      >
      > BEGIN
      >
      > INSERT INTO #temp_Table EXEC
      > sp_spaceused @table_name,
      > false;
      >
      > END
      >
      >
      >
      > FETCH NEXT FROM c1 INTO @table_name
      >
      > END
      >
      > CLOSE c1
      >
      > DEALLOCATE c1
      >
      >
      >
      > SELECT t1.*
      >
      > ,t2.schemaname
      >
      > FROM #temp_Table t1
      >
      > INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename
      > )
      >
      > ORDER BY schemaname,t1.tablename;
      >
      >
      >
      > DROP TABLE #temp_Table
      >
      >
      >
      > ***********
      >
      >
      >
      > ________________________________
      >
      > From: sqlcon@yahoogroups.com
      > [mailto:sqlcon@yahoogroups.com] On Behalf
      > Of Veer Ji Wangoo
      > Sent: Thursday, July 26, 2007 6:54 PM
      > To: sqlcon@yahoogroups.com
      > Subject: RE: MUGH-SQLCON No of Tables in a database
      >
      >
      >
      > Oops attachments not allowed.. please copy paste it
      > neatly in NON -HTM
      > page in your email..
      >
      > From: sqlcon@yahoogroups.com
      > <mailto:sqlcon%40yahoogroups.com>
      > [mailto:sqlcon@yahoogroups.com
      > <mailto:sqlcon%40yahoogroups.com> ] On
      > Behalf
      > Of Sunil Kak
      > Sent: Thursday, July 26, 2007 8:08 AM
      > To: sqlcon@yahoogroups.com
      > <mailto:sqlcon%40yahoogroups.com>
      > Subject: RE: MUGH-SQLCON No of Tables in a database
      >
      > Hi Shaik
      >
      > Find attached the script for the same
      >
      > Sunil kak
      >
      > ________________________________
      >
      > From: sqlcon@yahoogroups.com
      > <mailto:sqlcon%40yahoogroups.com>
      > <mailto:sqlcon%40yahoogroups.com>
      > [mailto:sqlcon@yahoogroups.com
      > <mailto:sqlcon%40yahoogroups.com>
      > <mailto:sqlcon%40yahoogroups.com> ] On
      > Behalf
      > Of shaik mansoor
      > Sent: Thursday, July 26, 2007 4:02 PM
      > To: sqlcon@yahoogroups.com
      > <mailto:sqlcon%40yahoogroups.com>
      > <mailto:sqlcon%40yahoogroups.com>
      > Subject: MUGH-SQLCON No of Tables in a database
      >
      > Hi Group,
      >
      > How can we see the no.of tables (count) in a
      > particular database and the
      > list of tables in a database.
      >
      > Thanks.
      > Shaik.
      >
      > ---------------------------------
      > Yahoo! oneSearch: Finally, mobile search that gives
      > answers, not web
      > links.
      >
      > [Non-text portions of this message have been
      > removed]
      >
      > DISCLAIMER:
      >
      ----------------------------------------------------------
      >
      >
      === message truncated ===




      ____________________________________________________________________________________
      Got a little couch potato?
      Check out fun summer activities for kids.
      http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz
    • shaik mansoor
      guys, select * from sysobjects where type= u select count(*) from sysobjects where type= u will work. Thanks, Shaik. Inderjeet Chana
      Message 2 of 6 , Aug 7, 2007
        guys,

        select * from sysobjects where type='u'

        select count(*) from sysobjects where type='u'

        will work.

        Thanks,
        Shaik.


        Inderjeet Chana <i_s_chana@...> wrote:

        what is the use of this script ...simple run this
        statement

        use <database>

        select count(*) from information_schema.tables where
        table_type='Base Table'
        go
        select table_name from information_schema.tables where
        table_type='Base Table'

        Inder

        --- Sunil Kak <sunilkak@...> wrote:

        >
        >
        >
        >
        > Below is the script
        >
        >
        >
        > ******
        >
        >
        >
        > DECLARE @table_name VARCHAR(500)
        >
        > DECLARE @schema_name VARCHAR(500)
        >
        > DECLARE @tab1 TABLE(
        >
        > tablename VARCHAR (500) collate
        > database_default
        >
        > ,schemaname VARCHAR(500) collate
        > database_default
        >
        > )
        >
        >
        >
        > CREATE TABLE #temp_Table (
        >
        > tablename sysname
        >
        > ,row_count INT
        >
        > ,reserved VARCHAR(50) collate
        > database_default
        >
        > ,data VARCHAR(50) collate database_default
        >
        > ,index_size VARCHAR(50) collate
        > database_default
        >
        > ,unused VARCHAR(50) collate database_default
        >
        >
        > )
        >
        >
        >
        > INSERT INTO @tab1
        >
        > SELECT Table_Name, Table_Schema
        >
        > FROM information_schema.tables
        >
        > WHERE TABLE_TYPE = 'BASE TABLE'
        >
        >
        >
        > DECLARE c1 CURSOR FOR
        >
        > SELECT Table_Schema + '.' + Table_Name
        >
        > FROM information_schema.tables t1
        >
        > WHERE TABLE_TYPE = 'BASE TABLE'
        >
        >
        >
        > OPEN c1
        >
        > FETCH NEXT FROM c1 INTO @table_name
        >
        > WHILE @@FETCH_STATUS = 0
        >
        > BEGIN
        >
        > SET @table_name = REPLACE(@table_name,
        > '[','');
        >
        > SET @table_name = REPLACE(@table_name,
        > ']','');
        >
        >
        >
        > -- make sure the object exists before
        > calling sp_spacedused
        >
        > IF EXISTS(SELECT id FROM sysobjects WHERE id
        > =
        > OBJECT_ID(@table_name))
        >
        > BEGIN
        >
        > INSERT INTO #temp_Table EXEC
        > sp_spaceused @table_name,
        > false;
        >
        > END
        >
        >
        >
        > FETCH NEXT FROM c1 INTO @table_name
        >
        > END
        >
        > CLOSE c1
        >
        > DEALLOCATE c1
        >
        >
        >
        > SELECT t1.*
        >
        > ,t2.schemaname
        >
        > FROM #temp_Table t1
        >
        > INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename
        > )
        >
        > ORDER BY schemaname,t1.tablename;
        >
        >
        >
        > DROP TABLE #temp_Table
        >
        >
        >
        > ***********
        >
        >
        >
        > ________________________________
        >
        > From: sqlcon@yahoogroups.com
        > [mailto:sqlcon@yahoogroups.com] On Behalf
        > Of Veer Ji Wangoo
        > Sent: Thursday, July 26, 2007 6:54 PM
        > To: sqlcon@yahoogroups.com
        > Subject: RE: MUGH-SQLCON No of Tables in a database
        >
        >
        >
        > Oops attachments not allowed.. please copy paste it
        > neatly in NON -HTM
        > page in your email..
        >
        > From: sqlcon@yahoogroups.com
        > <mailto:sqlcon%40yahoogroups.com>
        > [mailto:sqlcon@yahoogroups.com
        > <mailto:sqlcon%40yahoogroups.com> ] On
        > Behalf
        > Of Sunil Kak
        > Sent: Thursday, July 26, 2007 8:08 AM
        > To: sqlcon@yahoogroups.com
        > <mailto:sqlcon%40yahoogroups.com>
        > Subject: RE: MUGH-SQLCON No of Tables in a database
        >
        > Hi Shaik
        >
        > Find attached the script for the same
        >
        > Sunil kak
        >
        > ________________________________
        >
        > From: sqlcon@yahoogroups.com
        > <mailto:sqlcon%40yahoogroups.com>
        > <mailto:sqlcon%40yahoogroups.com>
        > [mailto:sqlcon@yahoogroups.com
        > <mailto:sqlcon%40yahoogroups.com>
        > <mailto:sqlcon%40yahoogroups.com> ] On
        > Behalf
        > Of shaik mansoor
        > Sent: Thursday, July 26, 2007 4:02 PM
        > To: sqlcon@yahoogroups.com
        > <mailto:sqlcon%40yahoogroups.com>
        > <mailto:sqlcon%40yahoogroups.com>
        > Subject: MUGH-SQLCON No of Tables in a database
        >
        > Hi Group,
        >
        > How can we see the no.of tables (count) in a
        > particular database and the
        > list of tables in a database.
        >
        > Thanks.
        > Shaik.
        >
        > ---------------------------------
        > Yahoo! oneSearch: Finally, mobile search that gives
        > answers, not web
        > links.
        >
        > [Non-text portions of this message have been
        > removed]
        >
        > DISCLAIMER:
        >
        ----------------------------------------------------------
        >
        >
        === message truncated ===

        __________________________________________________________
        Got a little couch potato?
        Check out fun summer activities for kids.
        http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz





        ---------------------------------
        Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.

        [Non-text portions of this message have been removed]
      Your message has been successfully submitted and would be delivered to recipients shortly.