To get a list of all columns within a database, you can use the ANSI compliant INFORMATION_SCHEMA.COLUMNS system view. SELECT * FROM INFORMATION_SCHEMA.COLUMNS In order to get all the columns in all the databases however, you need to loop through the databases. To do this, you can use the undocumented sp_MSForEachDB procedure that Microsoft ships. [...]
Archive of posts filed under the TSQL category.
List All Databases
To list all databases in a SQL Server instance, run the following: SELECT * FROM sys.sysdatabases This also outputs metadata about each database including createdate, compatibility level, and the MDF filename. Popular search terms:sql Server 2008 list all databasesList all databases ownerstsql list of all databases in a sql instancesql server 2008 list databases with [...]
List All Tables in a Database
There are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll show you the simplest way first which is probably the most supported way. SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME, * [...]
Using Coalesce
The coalesce function is used to find the first non-null value. The function takes limitless number of parameters in order to evaluate the first non null. If all the parameters are null, then COALESCE will also return a NULL value. — hard coded example SELECT MyValue = COALESCE(NULL, NULL, ‘abc’, 123) The example above returns [...]
SQL Insert Into Statement
The “insert into” statement is used in order to insert data into an existing table. The syntax for this is fairly simple. In the first section of the statement, you specify the table name and column names in which you are inserting data into. The second part is where the source of the data is [...]
SQL Date Comparison
When comparing the datetime datatype in SQL Server, it is important to maintain consistency in order to gaurd against SQL interpreting a date differently than you intend. In at least one occasion I have seen someone specify a short format for a date, like (1/4/08) only to find that SQL interpreted the month as the [...]
SQL Server For Each Row Next
It is difficult for me to write this particular article and I’ll tell you why. If you don’t care then just skip down to the example, but here goes anyway. It is very rare that you should have to perform looping in SQL. There are certain situations that do require it, and they are mostly [...]
SQL Rank
The SQL Rank function was introduced in SQL Server 2005 and is part of a family of ranking functions. In order to explain the SQL Rank function, we need to look at it in context with the other rank functions. RANK DENSE_RANK ROW_NUMBER NTILE This list may seem overwhelming, however most of the ranking functions [...]
SQL Server SELECT INTO
The most efficient method of copying a result set into a new table is to use the SELECT INTO method. This method also follows a very simple syntax. SELECT * INTO dbo.NewTableName FROM dbo.ExistingTable Once the query above is executed, all the columns and data in the table ExistingTable (along with their datatypes) will be [...]
SQL Server Begin Try
The try catch methodology of programming is a great innovation for SQL 2005+. The first question you should ask yourself before using Try/Catch should be “why?”. Why am I going to use Try/Catch? Personally, I have found a few uses, however I must say I do fall into the category of not being a developer [...]
