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. [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 ...

Find SQL Server Port

The default port that SQL Server listens on is 1433. This port however is not the same port in all cases as we will come to find. SQL Server has three different ways to communicate. These include: TCP Ports Named Pipes Shared Memory VIA (Virtual Interface Adapater) – Deprecated   When communicating, SQL Server can actually use a combination of all three of these protocols for different sessions depending on whether or not they are enabled. You can find more specifics regarding these protocols here. If that doesn’t make things complicated enough, within the TCP protocol, static or dynamic ports can be defined. If the communication is set to a static port (most common) then it is by default 1433. If it is set to dynamic, then it typically uses a range of ports. It is this range of ports that is not easy to determine, unless you use a command at the command line and correlate the associated process ID with that of the SQL Server instance you are investigating. The easy way to find the ports is to use the following script which will enumerate all ports that all SQL Server instances are using. (Once you download the script, rename it so SQLListeningPorts.vbs). The script is also shown below. All you need to do is cut and paste it into notepad, save it with a .vbs extension and double click it. (or right click and ‘open with’ Microsoft Script Host). Show All SQL Ports VB Script [cc lang=”vb”] […]

Transform Each Row Into XML

This neat and simple little trick will help to transform each row in a table or query into an XML row. The simplest way is to use the FOR XML clause. However if you want to return the XML as a separate column in the table, you need to perform a self-join on the table itself. [cc lang=”sql”] SELECT TOP 100 EmployeeID, EmployeeXML = ( SELECT EmpLastName = LastName ,e_xml.* FROM dbo.Employee e_xml WHERE e_xml.EmployeeID = emp.EmployeeID FOR XML PATH (”) ) FROM dbo.Employee emp [/cc] The EmpLastName assignment shows how you can assign your own custom XML tags based on each column. Continue reading ...

Using XQuery

XQuery in SQL Server was introduced in version 2005, along with so many major advancements. The ability to store and query XML has changed the game of SQL Server and has made it what it more of what it should be, a better workflow / storage engine. There are some things to consider prior to introducing XML into the database. The most plausible question you can ask yourself when using XML instead of a table is, “do these XML elements have any use as individual items, or do they only have meaning within the context of this XML document”. Weighing that question, along with the ease of XML portability will hopefully give you the answer you need. Now to use XQuery. SQL Server only supports a subset of XQuery so you don’t want to get too fancy with your needs. There are certain functions (like substring) that are not supported, however most of the missing functionality can be addressed using SQL Server functions. If you do need these added functionalities, then you also may want to consider whether the XML manipulation using SQL is the right approach. While XML is quite useful, and while SQL Server can (architecturally) be used as a workflow engine, when it comes to certain items like display functions, SQL Server is not the right choice. This following example is from a rules engine I am creating. The XML itself represents a rule, and the XQuery below parses the rule into a table. The beauty of […]