The following query uses the
sys.objects
catalog view to return all database objects that have been modified in the last 10 days.
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 10
ORDER BY modify_date;
The following example queries the
INFORMATION_SCHEMA.COLUMNS
view to return all columns for the
Contact
table in the
AdventureWorks
database.
SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';
This Query returns the owner of each object in a schema.
Before you run the following query, replace all occurences of
<database_name>
and
<schema_name>
with valid names.
SELECT 'OBJECT' AS entity_type
,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
,name
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'TYPE' AS entity_type
,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
,name
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type
,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
,xsc.name
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
This query can be used to get record count of all tables in the current database.
SELECT
T.TABLE_NAME AS [TABLE NAME], MAX(I.ROWS) AS [RECORD COUNT]
FROM SYSINDEXES I, INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME = OBJECT_NAME(I.ID)
AND T.TABLE_TYPE = 'BASE TABLE'
GROUP BY T.TABLE_SCHEMA, T.TABLE_NAME
This query can be used to List all tables/views with columns.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA,
TABLE_NAME,
ORDINAL_POSITION
GO
Gaurav Dudeja has done B-Tech from ABES Engg College, Ghaziabad, Uttar Pradesh, India . He is an interested, committed, creative Software professional having more than 3 years of solid experience in web-based and windows based solutions in Microsoft Technologies using .NET 2.0, .NET 3.0 , .NET 3.5, ASP.NET 2.0, ASP.NET 3.5 C# 2.0, AJAX, Web Services, MS SQL Server 2005, WSS (Windows Sharepoint Server 3.0 ). He is also an MCP (Microsoft Certified Professional) and MCTS (Microsoft Certified Technology Specialist) on Web Development (.NET 2.0 ). He has good knowledge of Object Oriented Programming, 3-Tier Architecture and Design Patterns as well as good command over IIS (IIS 5.1,IIS 6.0, IIS 7.0) and deployment of Application on Live Production Environment.