Click here to Skip to main content
Click here to Skip to main content

Tagged as

Some internal SQL Sever Queries

, 22 Feb 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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_dateFROM sys.objectsWHERE modify_date >...
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 and 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gaurav Dudeja India
Technical Lead Samsung India Electronics Pvt. Ltd.
India India
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.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralReason for my vote of 5 very Pinmembernikhi _singh4-Jan-12 17:19 
GeneralA little Info PinmvpMd. Marufuzzaman20-Feb-10 6:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 23 Feb 2010
Article Copyright 2010 by Gaurav Dudeja India
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid