002_ system table Query (sysdatabases, etc.)

002_ system table Query (sysdatabases, etc.)

--1. Get all database names:

SELECT Name from Master. sysdatabases ORDER by Name

--2. Get all table names:

--select Name from DatabaseName. SysObjects Where xtype= ' U ' ORDER by Name

SELECT * FROM Sys. SysObjects Where xtype= ' U ' ORDER by Name

SELECT * from SysObjects WHERE Name = ' syscolumns '

--databasename: Represents the database 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 ')

--Operating system table **************************************************************

SELECT * FROM Msdb.dbo.sysjobs-stores information for individual scheduled jobs that will be executed by SQL Server Agent

SELECT * from Msdb.dbo.sysjobschedules-contains schedule information for jobs that will be executed by SQL Server Agent

SELECT * from Msdb.dbo.sysjobactivity; --Log current SQL Server Agent job activity and status

SELECT * from Msdb.dbo.sysjobservers-Stores the association or relationship of a specific job with one or more target servers

SELECT * from Msdb.dbo.sysjobsteps; --Contains information about the individual steps in the job to be performed by SQL Server Agent

SELECT * from Msdb.dbo.sysjobstepslogs; --a job step log that contains all SQL Server Agent job steps

SELECT * from Msdb.dbo.sysjobs_view; --Contains the name of the job, job_id and other information

SELECT * FROM Msdb.dbo.sysjobhistory-contains information about SQL Server Agent execution of scheduled jobs

SELECT * from Msdb.dbo.syscategories-contains categories that are used by SQL Server Management Studio to organize jobs, alerts, and operators

--System index related table **************************************************************

SELECT * FROM sys.indexes

The current settings for the index type, filegroup, or partition scheme ID and indexing options stored in the metadata.

SELECT * FROM Sys.index_columns

The column ID, the position within the index, the type (key or non-key), and the sort order (ASC or DESC).

SELECT * FROM Sys.spatial_index_tessellations

The type and tessellation scheme for each spatial index, and the basic information about each index.

SELECT * FROM Sys.spatial_indexes

Information about the tessellation scheme and parameters used by the spatial index.

SELECT * FROM Sys.stats

The statistics associated with the index, including the statistics name, and whether the name was created automatically or created by the user.

SELECT * FROM Sys.stats_columns

The column ID associated with the statistic.

SELECT * FROM Sys.xml_indexes

XML index types: Primary and secondary, and minor types and descriptions.

--All system tables

/*

Activesubscriptions

Batch

CachePolicy

Catalog

Chunkdata

Chunksegmentmapping

Configurationinfo

DataSource

Event

Executionlogstorage

History

Keys

Modeldrill

Modelitempolicy

Modelperspective

Notifications

Policies

Policyuserrole

Reportschedule

Roles

Runningjobs

Schedule

Secdata

Segment

Segmentedchunk

Serverparametersinstance

Snapshotdata

Subscriptions

subscriptionsbeingdeleted

Upgradeinfo

Users

*/

SELECT * from sys.databases

SELECT * from master.dbo.sysprocesses

002_ system table Query (sysdatabases, etc.)