Here is a simple method to convert a text string to numbers (or an integer). This method evaluates each value in a column to determine if the datatype is numeric. If it is, then it converts it to an integer. [cc lang=”sql”] SELECT CASE WHEN ISNUMERIC(PostalCode) > 0 THEN CAST(PostalCode AS INT) ELSE 0 END FROM SalesLT.Address [/cc] Now, what if the column values contain text characters and you want to strip those characters out and then convert the value to an integers. Well, luckily, there is a way to do this. Most of the examples to do this will loop the values using a while loop, however this example uses a dynamic numbers table in conjunction with a trick shown to me by Simon Sabin from his blog. This method will replace all the non numeric characters and convert the values to an integer. [cc lang=”sql”] — define max number of character values — in the string being evaluated DECLARE @MaxNumber INT = 5000 ;WITH Numbers AS ( SELECT 1 AS Num UNION ALL SELECT Num+1 FROM Numbers WHERE Num
Continue reading ...
To get a list of all columns within a database, you can use the ANSI compliant INFORMATION_SCHEMA.COLUMNS system view. [cc lang=”sql”] SELECT * FROM INFORMATION_SCHEMA.COLUMNS [/cc] 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. [cc lang=”sql”] sp_MSForEachDB @command1=’USE ?; SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS’ [/cc]
Continue reading ...
To list all databases in a SQL Server instance, run the following: [cc lang=”sql”] SELECT * FROM sys.sysdatabases [/cc] This also outputs metadata about each database including createdate, compatibility level, and the MDF filename.
Continue reading ...
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 ...
