INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view allows you to get information about all columns for all tables and views within a database. By default it will show you this information for every single table and view that is in the database.

ExplanationThis view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

The columns that this view returns are as follows:

Column name	Data type	Description
TABLE_CATALOG	nvarchar(128)	Table qualifier.
TABLE_SCHEMA	nvarchar(128)	Name of schema that contains the table.
TABLE_NAME	nvarchar(128)	Table name.
COLUMN_NAME	nvarchar(128)	Column name.
ORDINAL_POSITION	int	Column identification number. Note: In SQL Server 2005, these column IDs are consecutive numbers.
COLUMN_DEFAULT	nvarchar(4000)	Default value of the column.
IS_NULLABLE	varchar(3)	Nullability of the column. If this column allows for NULL, this column returns YES. Otherwise, NO is returned.
DATA_TYPE	nvarchar(128)	System-supplied data type.
CHARACTER_MAXIMUM_LENGTH	int	Maximum length, in characters, for binary data, character data, or text and image data. -1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see Data Types (Transact-SQL).
CHARACTER_OCTET_LENGTH	int	Maximum length, in bytes, for binary data, character data, or text and image data. -1 for xml and large-value type data. Otherwise, NULL is returned.
NUMERIC_PRECISION	tinyint	Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_PRECISION_RADIX	smallint	Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_SCALE	int	Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
DATETIME_PRECISION	smallint	Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
CHARACTER_SET_CATALOG	nvarchar(128)	Returns master. This indicates the database in which the character set is located, if the column is character data ortext data type. Otherwise, NULL is returned.
CHARACTER_SET_SCHEMA	nvarchar(128)	Always returns NULL.
CHARACTER_SET_NAME	nvarchar(128)	Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
COLLATION_CATALOG	nvarchar(128)	Always returns NULL.
COLLATION_SCHEMA	nvarchar(128)	Always returns NULL.
COLLATION_NAME	nvarchar(128)	Returns the unique name for the collation if the column is character data or text data type. Otherwise, NULL is returned.
DOMAIN_CATALOG	nvarchar(128)	If the column is an alias data type, this column is the database name in which the user-defined data type was created. Otherwise, NULL is returned.
DOMAIN_SCHEMA	nvarchar(128)	If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned.
DOMAIN_NAME	nvarchar(128)	If the column is a user-defined data type, this column is the name of the user-defined data type. Otherwise, NULL is returned.

(Source: SQL Server 2005 Books Online)

---

Here is an example of data that was pulled from the AdventureWorks database.  This data was pulled using this query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

To be able to show the output the results were broken into multiple pieces.

---

To query for just one table you can use a query like this:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Address'

INFORMATION_SCHEMA.TABLES

The INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database.

ExplanationThis view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

The columns that this view returns are as follows:

Column name	Data type	Description
TABLE_CATALOG	nvarchar(128)	Table qualifier.
TABLE_SCHEMA	nvarchar(128)	Name of schema that contains the table.
TABLE_NAME	sysname	Table name.
TABLE_TYPE	varchar(10)	Type of table. Can be VIEW or BASE TABLE.

(Source: SQL Server 2005 Books Online)

---

Here is an example of data that was pulled from the AdventureWorks database.  This data was pulled using this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES

---

To only show a list of tables you would use this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

---

To only show a list of only the view you would use this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW'

INFORMATION_SCHEMA VIEW

The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database.  These views can be found in the master database under Views / System Views and be called from any database in your SQL Server instance.  The reason these were developed was so that they are standard across all database platforms.  In SQL 2005 and SQL 2008 these Information Schema views comply with the ISO standard.

Following is a list of each of the views that exist.

• INFORMATION_SCHEMA.CHECK_CONSTRAINTS
• INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
• INFORMATION_SCHEMA.COLUMN_PRIVILEGES
• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
• INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
• INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
• INFORMATION_SCHEMA.DOMAINS
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE
• INFORMATION_SCHEMA.PARAMETERS
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
• INFORMATION_SCHEMA.ROUTINE_COLUMNS
• INFORMATION_SCHEMA.ROUTINES
• INFORMATION_SCHEMA.SCHEMATA
• INFORMATION_SCHEMA.TABLE_CONSTRAINTS
• INFORMATION_SCHEMA.TABLE_PRIVILEGES
• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
• INFORMATION_SCHEMA.VIEW_TABLE_USAGE
• INFORMATION_SCHEMA.VIEWS

These views can be used from any of your databases.  So if you want to gather data about Tables from the AdventureWorks database you would issue the following in that database.

USE AdventureWorks GO SELECT * FROM INFORMATION_SCHEMA.TABLES

FOR MORE DETAILS  GO...

http://www.mssqltips.com/sqlservertutorial/179/sql-server-informationschema-views/ 

Temporary table vs Table variable vs derived Table

Four options available for temporary matrix storage: Derived Table and Temporary Table are the oldest, followed by Table Variable and the latest is CTE which can also be recursive.




Temp tables,
          IO Operation - HIGH
          Explicit Indexing is allowed
          Constraints are allowed
          Need not create or declare, we can use the INTO clause to create this object.        
          Reusable across the batch but with current session
          Recommended when the data is huge & if there is any manipulation on the result
          Supports concurrency users
          Bit slower than table variables.


Table variables,
          IO Operation – LOW or NA
          Explicit Indexing not allowed – (Primary/Unique key can create index)
          Constraints are allowed
          Have to declare the variable
          Only used by the current batch scope
          Recommended when the data is less & if there is any manipulation on the result
          Supports concurrency users
Faster when the number of records is less


Derived tables,
          IO Operation – NA
          Declaration – NA
          Explicit Indexing – NA
          Constraints - NA
          Only on the current query(line)
          If it is not reusable data then we can go for it
          Recommended for inline quires
          Supports concurrency users

What is Derived Tables in SQL

The power of SQL Server never fails to amaze me; it literally seems that you can do just about anything in SQL. All you need is a little creativity and knowledge of the syntax, and you can put the power of SQL behind your web application. One of the neatest things I've yet done with SQL Server is using derived tables.

If you've used a VIEW before, you've used a more formal, more correct form of a derived table. For example, we could do the following:

 CREATE VIEW vwEmployeesFromNewYork AS
  SELECT * FROM Employee
  WHERE State = "NY"
 GO

Then if we wanted to see all of the Employees from New York with the last name Smith, ordered alphabetically, we could write:

 SELECT LastName, FirstName
 FROM vwEmployeesFromNewYork
 WHERE LastName = "Smith"
 ORDER BY FirstName

However, using derived tables, we could eliminate the view entirely. (Of course the view could be eliminated by simply adding an "AND State = "NY"" to the above WHERE clause, but what's important here is the concept, not the example!) Here is the same resultset as above but with the use of a derived table in place of a veiw:

 SELECT LastName, FirstName
 FROM
  (SELECT * FROM Employee
   WHERE State = "NY") AS EmployeeDerivedTable
 WHERE LastName = "Smith"
 ORDER BY FirstName

Isn't that neat? What we are doing is first getting the result set from our derived table (the SELECT statement in the FROM clause). Once we have that resultset, it is as though it was a table in itself. We then perform the SELECT on the derived table, returning our results! 

Pivots with Dynamic Columns in SQL Server 2005

Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:
PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):

CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)

SELECT * FROM SALES
 

Month             SaleAmount
----------------  -----------
January           100
February          200
March             300 

Suppose we wanted to convert the above into this:

 
January     February    March
----------- ----------  ----------
100         200         300

We can do this using the PIVOT operator, as follows:

SELECT  [January]
      , [February]
      , [March]
FROM    ( SELECT    [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:
In the first table I have the column names I want to use:

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')

Now I would like to retrieve data from these two tables, in the following format:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec 

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:

SELECT  tID
      , [Country]
      , [Day]
      , [Month]
FROM    ( SELECT    t2.tID
                  , t1.ColName
                  , t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId = t2.ColID
        ) p PIVOT ( MAX([Txt])
                    FOR ColName IN ( [Country], [Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.
In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].
Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + colName + ']',
                         '[' + colName + ']')
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.
Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

Executing this with

EXECUTE(@query)

will give us the expected result: a table that is pivoted and shows columns that were specified in a table:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec

What are master, msdb, tempdb databases in Sql Server?

Microsoft® SQL Server 2000 systems have four system databases: 

• master - The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. 
• tempdb - tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. 
By default, tempdb autogrows as needed while SQL Server is running. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb. 
• model - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. 
• msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.