Information_schema Part3
A Primer on INFORMATION_SCHEMA Views in SQL Server 2000 - Most commonly used queries on INFORMATION_SCHEMA views (Page 3 of 5 )
The following queries may help application programmers to get the most out of INFORMATION_SCHEMA views in a practical way. You can execute these queries on any database of any SQL Server 2000 instance.
The following command gives the list of tables available in the database:
select table_name
from information_schema.tables
where table_type='BASE TABLE'
The following command gives the list of columns of every table in the database, including their data types and widths. You can also use a WHERE clause, if you would like to deal with only one table.
select table_name,column_name, data_type, character_maximum_length as width
from information_schema.columns
order by table_name,ordinal_position
The following command gives the list of all views and their definitions from the database.
select table_name as view_name, view_definition from information_schema.views
The following command gives all the list stored procedures and their definitions (unless they are encrypted) available in the database. To get the list of stored functions just replace the word ‘PROCEDURE’ with ‘FUNCTION’.
select routine_name, routine_definition
from information_schema.routines
where routine_type='PROCEDURE'
The following command gives you all the foreign key constraints (relationships) together with their respective primary keys of parents.
select constraint_name as 'foreign key', unique_constraint_name as 'parent primary key'
from information_schema.referential_constraints
The following gives you all the lists of parameters of every routine (stored procedure or stored function) along with their data types and widths.
select specific_name as [routine name],parameter_name, data_type, character_maximum_length as [width]
from information_schema.parameters
order by specific_name,ordinal_position
The above examples are just to help you understand how this works. You can also design more complicated queries by joining the INFORMATION_SCHEMA views together according to the requirements.
------------------------------------------------------------------------------------------------
A Primer on INFORMATION_SCHEMA Views in SQL Server 2000 - Internals of INFORMATION_SCHEMA views (Page 4 of 5 )
Now that you have enough of an understanding of INFORMATION_SCHEMA views, let us move on to further internals of INFORMATION_SCHEMA views.
What made the INFORMATION_SCHEMA views so powerful? Are they re-written by Microsoft to help the application programmers? Do they having any relation to system stored procedures or functions? Can we create our own views together with INFORMATION_SCHEMA views and some system stored procedures?
Several questions could be extracted from our present knowledge on INFORMATION_SCHEMA. But, primarily there is a fundamental answer for any of the questions above.
INFORMATION_SCHEMA VIEWS ARE JUST THE VIEWS BASED ON EXISTING SYSTEM STORED PROCEDURES OR FUNCTIONS.
I hope the above answers almost all of the questions. Microsoft made our life easier by creating the INFORMATION_SCHEMA views on top of system stored procedures. Now, how do you know the internal query of a certain view present in INFORMATION_SCHEMA? This is a very simple and situational question according to the current topic. Of course, the answer is also simple, too. Let us consider the following.
I would like to know how Microsoft SQL Server team implemented INFORMATION_SCHEMA.TABLES view. Just type the following in your query analyzer (generally using ‘master’ database), and you should be able to see the result.
execute sp_helptext "information_schema.tables"
The result returned by that would be as following:
create view INFORMATION_SCHEMA.TABLES
as
select distinct
db_name() as TABLE_CATALOG
,user_name(o.uid)as TABLE_SCHEMA
,o.name as TABLE_NAME
,case o.xtype
when 'U' then 'BASE TABLE'
when 'V' then 'VIEW'
end as TABLE_TYPE
from
sysobjects o
where
o.xtype in ('U', 'V') and
permissions(o.id) != 0
I hope you understand the secret behind the INFORMATION_SCHEMA views now. They are nothing but the queries written by the SQL Server team to make our life easier, instead forcing us to remember a complicated query using the system stored procedures and functions.
------------------------------------------------------------------------------------------------
A Primer on INFORMATION_SCHEMA Views in
SQL Server 2000 - Summary (Page 5 of 5 )
From all the above sections of this article, we could conclude something like the following:
INFORMATION_SCHEMA views are easy to understand and remember. Use INFORMATION_SCHEMA views wherever possible and applicable.
Use system
stored procedures if and only if you cannot get an alternative from INFORMATION_SCHEMA views.
Using system stored procedures heavily in your application may cause versioning problems, when SQL
Server gets upgraded to a newer version.
You are always safe (at least better than using system stored procedures) using INFORMATION_SCHEMA views, even after an upgrade to a newer version.
Try to create your own
schema of views, for your own queries, which are very frequently being used on existing INFORMATION_SCHEMA views.
I also advise you to know and learn about the internal system table and system stored procedure hierarchy, to give you a solid understanding of database activities. Even though it is not so easy to remember all of those issues, at least the concept would help us to derive our own powerful, administrational and consolidated queries, which might be used very frequently. Generally, almost any SQL Server DBA would certainly know about almost all of the internal structures and hierarchies needed to make and keep the database stable in performance.
I would certainly be happy to receive your comments (or feedback) on this article at
jag_chat@yahoo.com.
End of the Post.
Information_schema Part2
A Primer on INFORMATION_SCHEMA Views in
SQL Server 2000 - High-level overview of all INFORMATION_SCHEMA views (Page 2 of 5 )
Now, the issue is how to get all the information
schema views in the form of a list. The following command helps to rescue us (but make sure to execute the command from the "master"
database).
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Views
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
ORDER BY TABLE_NAME
The above command lists out all the views you can use with information_schema. Following is a table which shows a brief description of all of the views available from the above command (which has been highly summarized from Books Online for the sake of convenience).
CHECK_CONSTRAINTS
Contains one row for each CHECK constraint in the current database
COLUMN_DOMAIN_USAGE
Contains one row for each column, in the current database, that has a user-defined
data type.
COLUMN_PRIVILEGES
Contains one row for each column with a privilege either granted to or by the current user in the current database
COLUMNS
Contains one row for each column accessible to the current user in the current database.
CONSTRAINT_COLUMN_USAGE
Contains one row for each column, in the current database, that has a constraint defined on it.
CONSTRAINT_TABLE_USAGE
Contains one row for each table, in the current database, that has a constraint defined on it.
DOMAIN_CONSTRAINTS
Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it
DOMAINS
Contains one row for each user-defined data type accessible to the current user in the current database
KEY_COLUMN_USAGE
Contains one row for each column, in the current database, that is constrained as a key
PARAMETERS
Contains one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.
REFERENTIAL_CONSTRAINTS
Contains one row for each foreign constraint in the current database. This information schema view returns information about the objects to which the current user has permissions.
ROUTINE_COLUMNS
Contains one row for each column returned by the table-valued functions accessible to the current user in the current database
ROUTINES
Contains one row for each stored procedure and function accessible to the current user in the current database.
SCHEMATA
Contains one row for each database that has permissions for the current user
TABLE_CONSTRAINTS
Contains one row for each table constraint in the current database.
TABLE_PRIVILEGES
Contains one row for each table privilege granted to or by the current user in the current database
TABLES
Contains one row for each table in the current database for which the current user has permissions
VIEW_COLUMN_USAGE
Contains one row for each column, in the current database, used in a view definition
VIEW_TABLE_USAGE
Contains one row for each table, in the current database, used in a view
VIEWS
Contains one row for views accessible to the current user in the current database
To use any of the above views, just add the respective view to the "information_schema" word separated with a dot as follows.
select * from information_schema.tables
Information_schema Part1
source :
http://www.aspfree.com/c/a/MS-SQL-Server/A-Primer-on-INFORMATIONSCHEMA-Views-in-SQL-Server-2000/A Primer on INFORMATION_SCHEMA Views in
SQL Server 2000 (Page 1 of 5 )
This article mainly concentrates on using INFORMATION_SCHEMA views effectively in
Microsoft SQL Server 2000, to retrieve the meta-information of a database.
Introduction
The word meta-data has several meanings and definitions. Meta-data describes the structure and meaning of data. In short, it explains the structure (or definitions) of existing data. It can also be defined as “data about data.”
From the Microsoft
SQL Server 2000 point of view, meta-data is something about exploring the structures (or definitions) of entire database
schema, including each and every object (tables, views, and so forth) present within that schema. Meta-data would include, for example, a list of tables, a list of the parameters of a particular stored procedure, a list of all user-defined functions, and so on.
Microsoft® SQL Server™ 2000 provides two methods for obtaining meta-data: system stored procedures and information schema views. System stored procedures will be generally (or heavily) used by DBAs to get any information about an SQL Server database. But, it would be quite hard to remember all of the internal stored procedures and their hierarchies. Another issue is that the system stored procedures tend to change from every version of SQL Server. Lots of differences (or enhancements) in system stored procedures exist from SQL Server 6.5 to 7.0 and even to 2000 (and of course to 2005) as well.
The better, the best and the simple way to get this type of information (meta-information about database) is to use INFORMATION_SCHEMA views. These views provide an internal, system table-independent view of the SQL Server meta-data. Information schema views allow applications to work properly, even though significant changes have been made to the system tables. These are most recommended for application programmers when they would like to play with database system information. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.
These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta-data for all of the data objects stored in that particular database.
Select 2nd largest item from a table
SELECT MIN(Salary) FROM Employee
WHERE Salary IN ( SELECT TOP 2 Salary FROM Employee)
This query retreives 2 nd top salary from the Employee table.
If u want 4 largest salary , then replace '2' in sub query with '4'
like :
SELECT MIN(Salary) FROM Employee
WHERE Salary IN ( SELECT TOP 4 Salary FROM Employee)
-------------------------------------------------------------------------------------
Information_schema
This is an important keyword to retrieve information about the Stored procedures ,
Tables (name,columns , sixe,constraints ...etc) .
select * from Information_schema.Columns
IT Ebook Home-Free IT ebooks Share & Exchange
IT Ebook Home-Free IT ebooks Share & ExchangeA very large collection of free e-books in the site.worth seeing !
Testing Process
Testing Process In a software development process, there are five different phases. They are
-Requirement analysis-Design-Development (or) Coding -Testing-Maintenance.Here the testing comes in fourth phase. But the actual testing process begins during the first phase itself i.e. testing process begins during the Requirement analysis phase itself.
The steps in the testing process are as follows.
1. Requirement analysisTesting should begin in the requirements phase of the software life cycle (SDLC). The actual requirement should be understand clearly with the help of Requirement Specification documents, Functional Specification documents, Design Specification documents, Use case Documents etc.
During the requirement analysis the following should be considered.
-Are the definitions and descriptions of the required capabilities precise?
-Is there clear delineation between the system and its environment?
-Can the requirements be realized in practice?
-Can the requirements be tested effectively?
2.
Test Planning During this phase Test Strategy, Test Plan, Test Bed will be created.A test plan is a systematic approach in testing a system or software.
The plan should identify:-
-Which aspects of the system should be tested?
-Criteria for success.
-The methods and techniques to be used.
-Personnel responsible for the testing.
-Different Test phase and Test Methodologies
-Manual and Automation Testing
-Defect Mgmt, Configuration Mgmt, Risk Mgmt. Etc
-Evaluation & identification
–Test, Defect tracking tools
3.
Test Environment SetupDuring this phase the required environment will be setup will be done.
The following should also be taken in account.
- Network connectivity’s
-All the Software/ tools Installation and configuration
-Coordination with Vendors and others
4.
Test DesignDuring this phase
-Test Scenarios will be identified.
-Test Cases will be prepared.
-Test data and Test scripts prepared.
-Test case reviews will be conducted.
5.
Test AutomationIn this phase the requirement for the automation will be identified.
The tools that are to be used will be identified.
Designing framework, scripting, script integration, Review and approval will be undertaken in this phase.
6.
Test Execution and Defect TrackingTesters execute the software based on the plans and tests and report any errors found to the development team.
In this phase
-Test cases will be executed.
-Test Scripts will be tested.
-Test Results will be analyzed.
-Raised the defects and tracking for its closure.
7.
Test ReportsOnce testing is completed, testers generate metrics and make final reports on their test effort and whether or not the software tested is ready for release.
-Test summary reports will be prepared
-Test Metrics and process Improvements made
-Build release-Receiving acceptance
Source : http://www.testingprojects.com
Co-Related Queries
What is a Correlated Subquery?
A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.
A correlated subquery will be executed many times while processing the T-SQL statement that contains the correlated subquery. The correlated subquery will be run once for each candidate row selected by the outer query. The outer query columns, referenced in the correlated subquery, are replaced with values from the candidate row prior to each execution. Depending on the results of the execution of the correlated subquery, it will determine if the row of the outer query is returned in the final result set.
Using a Correlated Subquery in a WHERE Clause
Suppose you want a report of all "OrderID's" where the customer did not purchase more than 10% of the average quantity sold for a given product. This way you could review these orders, and possibly contact the customers, to help determine if there was a reason for the low quantity order. A correlated subquery in a WHERE clause can help you produce this report. Here is a SELECT statement that produces the desired list of "OrderID's":select distinct OrderId
from Northwind.dbo.[Order Details] OD
where
Quantity The correlated subquery in the above command is contained within the parenthesis following the greater than sign in the WHERE clause above. Here you can see this correlated subquery contains a reference to "OD.ProductID". This reference compares the outer query's "ProductID" with the inner query's "ProductID". When this query is executed, the SQL engine will execute the inner query, the correlated subquery, for each "[Order Details]" record. This inner query will calculate the average "Quantity" for the particular "ProductID" for the candidate row being processed in the outer query. This correlated subquery determines if the inner query returns a value that meets the condition of the WHERE clause. If it does, the row identified by the outer query is placed in the record set that will be returned from the complete T-SQL SELECT statement.
The code below is another example that uses a correlated subquery in the WHERE clause to display the top two customers, based on the dollar amount associated with their orders, per region. You might want to perform a query like this so you can reward these customers, since they buy the most per region.
select CompanyName, ContactName, Address,
City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in (
select top 2 InnerC.CustomerId
from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
on OD.OrderId = O.OrderID
join Northwind.dbo.Customers InnerC
on O.CustomerID = InnerC.CustomerId
Where Region = OuterC.Region
group by Region, InnerC.CustomerId
order by sum(UnitPrice * Quantity * (1-Discount)) desc
)
order by Region
Here you can see the inner query is a correlated subquery because it references "OuterC", which is the table alias for the "Northwind.DBO.Customer" table in the outer query. This inner query uses the "Region" value to calculate the top two customers for the region associated with the row being processed from the outer query. If the "CustomerID" of the outer query is one of the top two customers, then the record is placed in the record set to be returned.
Correlated Subquery in the HAVING Clause
Say your organizations wants to run a yearlong incentive program to increase revenue. Therefore, they advertise to your customers that if each order they place, during the year, is over $750 you will provide them a rebate at the end of the year at the rate of $75 per order they place. Below is an example of how to calculate the rebate amount. This example uses a correlated subquery in the HAVING clause to identify the customers that qualify to receive the rebate. Here is my code for this query:
select C.CustomerID, Count(*)*75 Rebate
from Northwind.DBO.Customers C
join
Northwind.DBO.Orders O
on c.CustomerID = O.CustomerID
where Datepart(yy,OrderDate) = '1998'
group by C.CustomerId
having 750 < orderid =" OD.OrderID" customerid =" C.CustomerId">By reviewing this query, you can see I am using a correlated query in the HAVING clause to calculate the total order amount for each customer order. I use the "CustomerID" from the outer query and the year of the order "Datepart(yy,OrderDate)", to help identify the Order records associated with each customer, that were placed the year '1998'. For these associated records I am calculating the total order amount, for each order, by summing up all the "[Order Details]" records, using the following formula: sum(UnitPrice * Quantity * (1-Discount)). If each and every order for a customer, for year 1998 has a total dollar amount greater than 750, I then calculate the Rebate amount in the outer query using this formula "Count(*)*75 ".
SQL Server's query engine will only execute the inner correlated subquery in the HAVING clause for those customer records identified in the outer query, or basically only those customer that placed orders in "1998".
Performing an Update Statement Using a Correlated Subquery
A correlated subquery can even be used in an update statement. Here is an example:create table A(A int, S int)
create table B(A int, B int)
set nocount on
insert into A(A) values(1)
insert into A(A) values(2)
insert into A(A) values(3)
insert into B values(1,1)
insert into B values(2,1)
insert into B values(2,1)
insert into B values(3,1)
insert into B values(3,1)
insert into B values(3,1)
update A
set S = (select sum(B)
from B
where A.A = A group by A)
select * from A
drop table A,B
Here is the result set I get when I run this query on my machine:A S
----------- -----------
1 1
2 2
3 3
In my query above, I used the correlated subquery to update column A in table A with the sum of column B in table B for rows that have the same value in column A as the row being updated.
Conclusion
Let me summarize. A subquery and a correlated subquery are SELECT queries coded inside another query, known as the outer query. The correlated subquery and the subquery help determine the outcome of the result set returned by the complete query. A subquery, when executed independent of the outer query, will return a result set, and is therefore not dependent on the outer query. Where as, a correlated subquery cannot be executed independently of the outer query because it uses one or more references to columns in the outer query to determine the result set returned from the correlated subquery. I hope that you now understand the different of subqueries and correlated subqueries, and how they can be used in your T-SQL code.
Trap window close event for IE browser
http://www.dotnetjunkies.com/WebLog/familjones/archive/2004/04/06/10884.aspxLet's say you want to trap the window close event for the web browser so that you can give a confirmation dialog asking if the user is sure to leave the page. The problem is that there is no onclose event for the window object. The closest event might be onunload since it fires immediately before the
window object is unloaded. However, when the onunload event fires it is too late to display a JavaScript alert. Therefore, we need an event that fires prior to a page being unloaded, which is onbeforeunload. Define onbeforeunload event in your page element as follows:
Then, add the following JavaScript code into the section of your ASPX page:
Trap window close event for IE browser
Let's say you want to trap the window close event for the web browser so that you can give a confirmation dialog asking if the user is sure to leave the page. The problem is that there is no onclose event for the window object. The closest event might be onunload since it fires immediately before the
window object is unloaded. However, when the onunload event fires it is too late to display a JavaScript alert. Therefore, we need an event that fires prior to a page being unloaded, which is onbeforeunload. Define onbeforeunload event in your page element as follows:
Then, add the following JavaScript code into the section of your ASPX page:
The trick here is to check clientY property of the event object, which is used to set or retrieve the y-coordinate of the mouse pointer's position relative to the client area of the window, excluding window decorations and scroll bars. This way, you can detect if the user clicked on X button to close the page, or clicked on Refresh button to refresh the page, etc. This approach does not handle key events such as Alt-F4 that lets the user close the window by using the keyboard. You have to handle keyboard events separately and this issue might be a topic for another article...
Famil Jones,
Karamasoft
Developing Entity Relationship Diagrams
Developing Entity Relationship DiagramsA brief guide for creating ER Diagrams.(Theoritical + example)
Optimizing database performance, part 1: Partitioning and indexing
Optimizing database performance, part 1: Partitioning and indexing
irt.org - JavaScript Form FAQ Knowledge Base
irt.org - JavaScript Form FAQ Knowledge Base
