September 25, 2012 at 11:24 am
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
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
September 25, 2012 at 11:35 am
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
provided error---
Column name or number of supplied values does not match table definition.
September 25, 2012 at 11:43 am
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
September 25, 2012 at 12:07 pm
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply