Monthly Archives: March 2015

How to find columns from all Tables of a Database

If your job is to create reports using SQL chances are you have or will encounter this situation:

You’ve been asked to prepare a report, but the person who has asked for the report simply has a list of fields they want and they have no idea where those fields come from. They may have received previous reports in the past, so they know the fields exist, but they cannot provide any of the SQL queries used to create these reports as an example.

You, the developer, may not be familiar with that particular area of the business or associated data sources. Possibly because you typically prepared financial reports and this request has come from the operations or marketing departments.

So the first step is to locate these columns within the database.

The following query will return the Table Name, Schema Name and Column Name from the database.

In the example below all instances where the column name equals CustomerID, OrderID, OrderDate will be returned. Also Column names that contain the word Status or Promotion will also be return. Simply change or add additional columns names as needed. 

USE [YourDatabaseName];
GO

SELECT T.NAME AS TableName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,C.NAME AS ColumnName
FROM SYS.TABLES AS t
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
WHERE C.NAME = 'CustomerID'
	OR C.NAME = 'OrderID'
	OR C.NAME = 'OrderDate'
	OR C.NAME LIKE '%Status%'
	OR C.NAME LIKE '%Promotion%'
ORDER BY SchemaName
	,TableName;

 

That should help get you started in preparing the report.

How to dynamically pull all text file names from a folder into a SQL Server table

So here’s a quick out of the box solution for . . . you guessed it, dynamically pulling all text files from a folder into a SQL Server table. On it’s own this script isn’t very powerful but it can be paired with SQL queries to make some powerful functionality.

To use simply change the @path variable to the directory path you’re targeting and as it states in the comments . . .

-- NOTE: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
-- *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED

Sayōnara


— THIS QUERY USES CMD TO CAPTURE ALL THE FILES IN A FOLDER
— AND WRITE THE FILE NAMES TO A TABLE
— THE QUERY REQUIRES THAT xp_cmdShell BE ENABLED TO WORK
— THE FOLLOWING ENABLES xp_cmdShell:
— TO ALLOW ADVANCED OPTIONS TO BE CHANGED
EXEC sp_configure 'show advanced options'
,1
GO
— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR ADVANCED OPTIONS
RECONFIGURE
GO
— TO ENABLE THE FEATURE
EXEC sp_configure 'xp_cmdshell'
,1
GO
— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR THIS FEATURE
RECONFIGURE
GO
— THE FOLLOWING CREATES A TABLE AND POPULATES IT WITH THE FILES LOCATED IN THE SPECIFIED DIRECTORY
IF OBJECT_ID('ALLFILENAMES', 'U') IS NOT NULL
DROP TABLE ALLFILENAMES
CREATE TABLE ALLFILENAMES (
WHICHPATH VARCHAR(255)
,WHICHFILE VARCHAR(255)
)
DECLARE @filename VARCHAR(255)
,@path VARCHAR(255)
,@cmd VARCHAR(1000)
— GET THE LIST OF FILES TO PROCESS
— NOTES: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
— *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED
SET @path = 'C:\Users\Data\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES (WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES
SET WHICHPATH = @path
WHERE WHICHPATH IS NULL
SELECT *
FROM ALLFILENAMES

How to increase the performance of your Android TV Stick

So basically all android TV sticks (ATS’s), boxes and any other android device you connect to your TV has been somewhat hacked together to deliver a product the Android OS wasn’t explicitly designed for. (Not that it doesn’t do a great job)

The Android OS was designed for mobile devices but what constitutes a mobile device?

Well one can differentiate a mobile device as a mobile device by two distinguishing characteristics, one it has a built-in display and two it’ll be battery powered.

Android mini computers have neither.

As mentioned above ATS’s use a TV for the display and often ATS’s don’t have the drivers needed to recognised touch screen inputs. No touch screen inputs can have its drawbacks but the focus in this article is on the influence of not having a battery.

Android devices are constantly doing a juggling act between making the device run as fast and be as responsive as possible and not burning through the battery.

This balancing is primarily directed by the CPU governor. The Linux kernel has a number of CPU frequency governors, which can be looked on as rules that set the CPU frequency based on the selected governor and usage patterns. The frequency or clock rate is typically used as an indicator of the processor’s speed, i.e. how quickly it processes tasks. It is measured in the SI unit hertz. The higher the speed the better the performance and the worse the power consumption.

The best thing about the governors is that they have pre-sets, when the “performance” governor is active, the CPU frequency will be set to its maximum value, the “powersave” governor sets the CPU to its lowest frequency, the “ondemand” governor sets the CPU frequency depending on the current usage, etc.

But here’s the important part, because an ATS has no battery and it’s being power by the mains, there’s no need to set the governor to go easy on power consumption. So the governor should be set to performance at all times but by default (the device thinking it’s mobile) it’s probably not.

So how do you change the governor?

Well like everything else with android you use an app of course!

Note: You cannot change your CPU governor unless your phone is rooted and you have a ROM or app that lets you make a change. Also, different kernels (the intermediary software between your phone’s hardware and the operating system) offer different sets of governors.

There are several to choose from:

  • CPU tuner
  • No-frills CPU control
  • SetCPU
  • See here for more

I use CPU tuner as pictured below.

cpuTuner

Simply install CPU Tuner and set profile to “Performance” and Governor to “Full Speed” and you should be getting a little extra juice from your device.

Slán