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. [cc lang=”sql”] SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME, * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ ORDER BY TABLE_SCHEMA + ‘.’ + TABLE_NAME [/cc] This method makes use of documented INFORMATION_SCHEMA system view. The next method makes use of two not so well documented system views. [cc lang=”sql”] SELECT FullName = s.name + ‘.’ + t.name ,SchemaName = s.name ,TableName = t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id ORDER BY s.name, t.name [/cc] There is really no difference between these two methods. The third method uses the sp_tables stored procedure and passes in the parameter of ‘TABLE’. Though the parameter being passes looks funny, this is how to make it work. [cc lang=”sql”] EXEC sp_tables @table_type = “‘TABLE’” [/cc] Show all Tables with Rowcount and Dataspace This method is the most extensive. Not only will it show you all the tables, but it will also display the rowcount and datasize in Megabytes. [cc lang=”sql”] SELECT * FROM ( SELECT TableName = t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME ,[RowCount] = SUM(sp.[rows]) ,Megabytes = (8 * SUM(CASE WHEN sau.type != 1 THEN sau.used_pages WHEN sp.index_id < 2 THEN sau.data_pages ELSE 0 END)) / 1024 FROM INFORMATION_SCHEMA.TABLES t JOIN sys.partitions sp ON […]
Continue reading ...
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. [cc lang=”sql”] — hard coded example SELECT MyValue = COALESCE(NULL, NULL, ‘abc’, 123) [/cc] The example above returns back ‘abc’ as it is the first non null value. When would you use COALESCE you ask? Well, the most common scenario I use it in is when I am joining two or more tables together and the tables all contain an acceptable value for, say, firstname. However if firstname is null, in the first table, we will want to use it from the second table, and so forth. [cc lang=”sql”] SELECT FirstName = COALESCE (a.FirstName, b.First_Name, c.Fname) FROM HRUsers a LEFT JOIN MarketingUsers b ON b.EmployeeID = a.EmployeeID LEFT JOIN SalesUsers c ON c.EmployeeID = a.EmployeeID [/cc] This example basically says, if FirstName is populated in the HRUsers table, we want to use that one first, otherwise, use the First_Name field in MarketingUsers, or if that is null, take the FName from SalesUsers. It should be noted that if only comparing two values, the ISNULL function has been proven to be quicker.
Continue reading ...
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 coming from. There are actually two different ways to specify source data. One way is using a SELECT statement, and the other way using the VALUES statement. Inserting using the VALUES Statement The VALUES statement is typically used when either hard coding values to be inserted or when specifying variables to be inserted. The SELECT statement is typically used when querying data from an existing table. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) VALUES(‘Bill’, ‘Jones’) [/cc] We see here that the VALUES statement takes the actual column values to be inserted into the Customer table. Using this method, you can only insert one row at a time. With the advent of SQL Server 2008, the VALUES statement can actually insert multiple rows at one time by delimiting with a comma. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) VALUES (‘Bill’, ‘Jones’) ,(‘John’, ‘Smith’) [/cc] INSERT INTO Using the SELECT Statement The select statement is generally used when selecting from the table. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) SELECT FirstName = t.First_Name ,LastName = t.Last_Name FROM Customers_Import t [/cc] This statement will actually insert all the customers from the Customers_Import […]
Continue reading ...
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 year. A couple incidences similar to this has scared me enough to be very careful in my SQL Date comparisons. Most of the time when hard coding a date I define it out the way SQL Server defines it (at least according to SQL Profiler). Like this: [cc lang=”sql”] DECLARE @MyDate datetime SET @MyDate = ‘2000-01-04T00:00:00.000’ SELECT * FROM Customers WHERE ModifiedDate = @MyDate [/cc] When defined this way, SQL will have no option to interpret in any other way that what is presented. The downside is, yes, it’s long. However when hard coding dates, I’d personally rather be safe that sorry. I haven’t run into it, nor have I tested it, but I’d hate to have all my dates coded in a system changed just because the next version of SQL or any other system decided to interpret things differently. The other safe way to compare dates is to use SQL Server’s datepart function. Utilizing this method, you can never go wrong either. [cc lang=”sql”] SELECT * FROM Customers WHERE — Compare year DATEPART(yyyy, ModifiedDate) = 2000 — compare month AND DATEPART(mm, ModifiedDate) = 08 — compare day AND DATEPART(dd, ModifiedDate) = 01 [/cc] […]
Continue reading ...
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 back-end or maintenance related. Ok, now that I got that off my chest, here you go. In order to perform an execution which iterates performing a “for each row”, this is the easiest way for me: [cc lang=”sql”] SELECT RowNum = ROW_NUMBER() OVER(ORDER BY CustomerID) ,* INTO #Customers FROM SalesLT.Customer DECLARE @MaxRownum int SET @MaxRownum = (SELECT MAX(RowNum) FROM #Customers) DECLARE @Iter int SET @Iter = (SELECT MIN(RowNum) FROM #Customers) WHILE @Iter
Continue reading ...
