MS SQL queries all table rows, gets all database names, table names, field names

1. Get all database names

--select Name from Master. sysdatabases ORDER by Name--

2. Get all table names:
--select Name nametemp,* from TEST: SysObjects Where xtype= ' U ' ORDER by name--table name
----xtype= ' U ': represents all user tables;
----xtype= ' S ': denotes all system tables;

3. Get all field names:

SELECT Name from syscolumns WHERE id=object_id (' TableName ')

4. Querying all Table Rows
----SELECT * from TEST: Sysindexes--Indicates that the fine

Select A.name, b.rows--Query all table rows/test The name of the library
From TEST: sysobjects a
INNER JOIN TEST: sysindexes b on a.id = b.ID
where a.type = ' u '
and B.indid in (0, 1)
ORDER BY A.name

"Declare @N int set @n=10000

--by querying the clustered index query of the index table (indid=1, which reflects the fact that the data is stored in a clustered index as reflected in the query)
Select object_name (ID), rowcnt from sysindexes where Rowcnt>[email protected] and indid=1 and OBJECTPROPERTY (ID, ' Isusertable ') =1
--a nonclustered index query by querying the index table (indid=0, this is a query that embodies data that is stored in a data heap, a table with no clustered index)
Select object_name (ID), rowcnt from sysindexes where Rowcnt>[email protected] and indid=0 and OBJECTPROPERTY (ID, ' Isusertable ') =1
  
What do you mean, someone asked Indid?
Select object_name (ID), rowcnt from sysindexes where Rowcnt>[email protected] and indid<=1 and OBJECTPROPERTY (ID, ' Isusertable ') =1, nonclustered indexes are not necessarily
Have to.
Indid=1 query is based on the clustered index key value to query the Sysindexs, that is, there is no clustered index table, through the above query is not found.
Indid>1 (such as indid=2, indid=3, and so on) represent queries based on nonclustered index numbers, but tables that do not have clustered indexes still exist in large numbers (this is not
Of course, this query may have errors, in the production of index fragmentation and cleanup process does exist, as long as the index maintenance, reconstruction, then the error should be small, even there is no error

--objectproperty:

Http://technet.microsoft.com/zh-cn/library/ms176105.aspx

sysindexes detailed Content

Http://technet.microsoft.com/zh-cn/library/ms190283.aspx

Each index and table in the database occupies a row in the table. The table is stored in each database.

Column Name	Data Type	Description
Id	Int	The table ID (if indid = 0 or 255). Otherwise, the ID of the table to which the index belongs.
Status	Int	Internal system state information.
First	Binary (6)	A pointer to the first page or root page.
Indid	smallint	Index ID: 1 = Clustered Index >1 = Non-clustered 255 = Table entry with text or image data
Root	Binary (6)	If indid >= 1 and < 255, root is a pointer to the root page. If indid = 0 or indid = 255, root is a pointer to the last page.
Minlen	smallint	The minimum row size.
Keycnt	smallint	The number of keys.
GroupID	smallint	The filegroup ID on which to create the object.
Dpages	Int	If indid = 0 or indid = 1, dpages is the count of data pages that have been used. If indid = 255, it is set to 0. Otherwise, the count of indexed pages has been used.
Reserved	Int	If indid = 0 or indid = 1, reserved is the page count assigned to all indexes and table data. If indid = 255, reserved is the page count assigned to text or image data. Otherwise, the page count is assigned to the index.
Used	Int	If indid = 0 or indid = 1, used is the total number of pages used for all indexes and table data. If indid = 255, used is the page count used for text or image data. Otherwise, the page count is used for the index.
Rowcnt	bigint	Data-level row counts based on indid = 0 and indid = 1. If indid = 255, rowcnt is set to 0.
rowmodctr	Int	Counts the total number of rows inserted, deleted, or updated since the last update of the table.
Xmaxlen	smallint	The maximum row size.
Maxirow	smallint	Maximum non-leaf index row size.
Origfillfactor	tinyint	The starting fill factor value to use when creating the index. This value is not preserved, however, this value can be helpful if you need to re-create the index but not remember the fill factor you used originally.
Reserved1	tinyint	Keep.
Reserved2	Int	Keep.
FirstIAM	Binary (6)	Keep.
Impid	smallint	Keep. The index implementation flag.
Lockflags	smallint	Used to constrain the granularity of the index lock that is considered. For example, for a lookup table that is essentially read-only, you can set it to only table-level locking to minimize the cost of locking.
Pgmodctr	Int	Keep.
Keys	varbinary (816)	The list of column IDs that make up the index key.
Name	sysname	The table name (if indid = 0 or 255). Otherwise, the name of the index.
Statblob	Image	Statistics BLOB.
MaxLen	Int	Keep.
Rows	Int	The number of data-level rows based on indid = 0 and indid = 1, which is duplicated for indid >1. If indid = 255, rows is set to 0. This column is provided for backwards compatibility.

MS SQL queries all table rows, gets all database names, table names, field names