Post reply

detailed information of tables in all databses

  • September 25, 2012 at 11:24 am

    #282182

    hi all,

    I am looking for a query which can give us detailed information of the database tables in a single result set..

    sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'----------- this is the one which i used but unale to get the result set of all databases in a single result set...it is difficult to document with this result set..

  • September 25, 2012 at 11:28 am

    #1542000

    you need to create a global temp table, and instead of selecting , insert into the global temp table.

    CREATE TABLE ##TMP (

    [DB] NVARCHAR(128) NULL,

    [NAME] SYSNAME NOT NULL,

    [OBJECT_ID] INT NOT NULL,

    [PRINCIPAL_ID] INT NULL,

    [SCHEMA_ID] INT NOT NULL,

    [PARENT_OBJECT_ID] INT NOT NULL,

    [TYPE] CHAR(2) NOT NULL,

    [TYPE_DESC] NVARCHAR(60) NULL,

    [CREATE_DATE] DATETIME NOT NULL,

    [MODIFY_DATE] DATETIME NOT NULL,

    [IS_MS_SHIPPED] BIT NOT NULL,

    [IS_PUBLISHED] BIT NOT NULL,

    [IS_SCHEMA_PUBLISHED] BIT NOT NULL,

    [LOB_DATA_SPACE_ID] INT NULL,

    [FILESTREAM_DATA_SPACE_ID] INT NULL,

    [MAX_COLUMN_ID_USED] INT NOT NULL,

    [LOCK_ON_BULK_LOAD] BIT NOT NULL,

    [USES_ANSI_NULLS] BIT NULL,

    [IS_REPLICATED] BIT NULL,

    [HAS_REPLICATION_FILTER] BIT NULL,

    [IS_MERGE_PUBLISHED] BIT NULL,

    [IS_SYNC_TRAN_SUBSCRIBED] BIT NULL,

    [HAS_UNCHECKED_ASSEMBLY_DATA] BIT NOT NULL,

    [TEXT_IN_ROW_LIMIT] INT NULL,

    [LARGE_VALUE_TYPES_OUT_OF_ROW] BIT NULL)

    EXEC sp_msForEachdb 'INSERT INTO ##TMP

    select "?" AS db, * from [?].sys.tables'

    SELECT * FROM ##TMP ORDER BY db

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 25, 2012 at 11:35 am

    #1542003

    Column name or number of supplied values does not match table definition.

    this is the error it is giving me

  • September 25, 2012 at 11:39 am

    #1542005

    provided error---

    Column name or number of supplied values does not match table definition.

  • September 25, 2012 at 11:43 am

    #1542010

    the definition i created was based on sys.tables for SQL 2005;

    create your model yourself, and script out the table, then use that for your global table definition:

    select db_name()as db,*

    into _MyGlobalTempTableDefinition

    from sys.tables

    *Edit:

    for reference, 2008 has three additional columns:

    ...

    [IS_TRACKED_BY_CDC] BIT NULL,

    [LOCK_ESCALATION] TINYINT NULL,

    [LOCK_ESCALATION_DESC] NVARCHAR(60) NULL)

    Dbouble Edit:

    2012 adds one more column to the definition above:

    ...

    [IS_FILETABLE] BIT NULL)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 25, 2012 at 12:07 pm

    #1542019

    thanks a lot dude...and can i get all the database list with table count and used space schemas index etccc....

  • September 25, 2012 at 12:10 pm

    #1542023

    jag4u (9/25/2012)

    thanks a lot dude...and can i get all the database list with table count and used space schemas index etccc....

    I showed you the basics, but it's up to you to modify it to your needs;

    The technique is there: build your global temp tables, use sp_msForEachdb to get the data.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply