Archive for the ‘Hacks’ Category

Google Keep

March 6th, 2017

Everyone that knows me, knows I'm a Google Keep fan. It's the best app for taking notes out there and I'm taking some time out to tell you why.

It can order your notes by colour or category. You can filter by category or search a note by entering one or more keywords, as you would expect from a Google app. Data is stored in the could and accessible on keep.google.com so even when you're without your phone, you can still access Keep. It also means you can use your full keyboard to type lengthier notes. But here are a few features your note app may not have and a nifty way to use that feature.

Text grabbing from images

Google Keep can grab text from your images and it does so with about 80% success rate. While that's not very high, it means that you don't have to type everything that's on the images. But that's not what I use it for, really. I make photographs of my receipts - dishwasher, clothes, bag, wallet, toys ... and store them in Google keep. Then, I let Google keep grab the text et voila - my receipt is instantly searchable in Google Keep. Next time I need to grab a receipt, I can pick up my phone, open Google keep and search for "Intertoys" or the brand of my dishwasher.

Talk to your shopping list

You can talk to Google Keep. Just say "OK Google" and say "Add potatoes to my shopping list". It will create a list named "Shopping" and add potatoes to it.

Reminders

You can turn Google Notes into reminders that show up in your Google Calendar. But the best feature is that you can set the reminder to a location. I have a reminde set for my favourite train stations to "check out" so I don't forget to check out. Or I set a reminder for myself when I park my car that I also have to open my parking app and "stop parking".

Posted in Hacks | Comments (0)

Windows taskbar set to auto-hide but the task bar seems stuck.

March 8th, 2013

I have my taskbar set to auto-hide. I like it that way. It can be several rows or columns large, but still be very discreet and not take up space on my display.

However, the taskbar sometimes seems stuck and won't hide. There's a number of very good reasons why it would do that, but there's only a few good and quick ways to get rid the problem.

The quickest solution I found is the following.

Hit ctrl-shift-esc to bring up the task manager.
In the "Processes" tab, find the process named "explorer.exe". Click it, and then click the "End Process" button. In the resulting dialog box, choose "End Process". Now, in the Task Manager menu, choose File and then Run and enter "explorer.exe" and hit the enter key.

Your taskbar is back up and running with no problems.

Yes, the process above is also killing your internet explorer if you have them running in the same process, which is why you should either not use Internet Explorer or simply run the internet browser in a different process: windows-E, View, check "Launch folder windows in a separate process".

Posted in Hacks | Comments (2)

T-SQL: Find any string in any column in any table in a database.

January 19th, 2012

Have you ever found yourself in need of finding a certain string in an entire database? As a freelance BI consultant, this happens to me rather frequently when analysing where and how data is stored and how to retrieve it.

So I came up with this code. It lets you find a string in any column in any table on your database. It's a pretty length search if you have a lot of data and a lot of columns and tables, but it sure beats searching by hand.

Be warned! This may run a pretty long time on large databases.

  1.  
  2. DECLARE @searchString NVARCHAR(MAX)
  3. DECLARE @sqlString NVARCHAR(MAX)
  4. DECLARE @processedString NVARCHAR(MAX)
  5.  
  6. -- Insert your searchstring below
  7. -- Use \ as escape character
  8. -- examples:
  9. -- to search for 'I'm waiting', use 'I\'m waiting'
  10. -- to search for '25%' use '25\%'
  11.  
  12. SELECT
  13. @searchString = 'naar achter'
  14.  
  15. DECLARE @resultTable TABLE
  16. (
  17. table_name NVARCHAR(255),
  18. COL_NAME NVARCHAR(255),
  19. value_found NVARCHAR(MAX),
  20. OBJECT_ID INT,
  21. col_id INT
  22. )
  23.  
  24. SELECT
  25. @sqlString = '-- start',
  26. @processedString = ''
  27.  
  28. WHILE
  29. @sqlString > ''
  30. BEGIN
  31.  
  32. SELECT
  33. @sqlString = '',
  34. @processedString = ''
  35.  
  36. SELECT
  37. @processedString = @processedString +
  38. CASE
  39. WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
  40. THEN
  41. '
  42. select
  43. ''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
  44. ''' + col.name + ''',
  45. NULL as value_found,
  46. ' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
  47. ' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
  48. '
  49. ELSE
  50. ''
  51. END,
  52. @sqlString = @sqlString +
  53. CASE
  54. WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
  55. THEN
  56. '
  57. select
  58. top 1
  59. ''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
  60. ''' + col.name + ''',
  61. ' + col.name + ' as value_found,
  62. ' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
  63. ' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
  64. from
  65. ' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + '
  66. where
  67. ' + col.name + ' like ''%' + @searchString + '%'' escape ''\''
  68. '
  69. else
  70. ''
  71. end
  72. from
  73. sys.columns col
  74. inner join
  75. sys.tables tbl
  76. on col.object_id = tbl.object_id
  77. and col.user_type_id in (231, 167, 175, 239)
  78. -- nvarchar, varchar, char, nchar
  79. -- no text
  80. left join
  81. @resultTable rst
  82. on col.column_id = rst.col_id
  83. and col.object_id = rst.object_id
  84. where
  85. rst.table_name is null
  86.  
  87. print @sqlString
  88. print @processedString
  89.  
  90. insert into
  91. @resultTable
  92. exec
  93. (@sqlString)
  94.  
  95. insert into
  96. @resultTable
  97. exec
  98. (@processedString)
  99.  
  100. print '---8<-----------------------------------------------------------------------------------------------'
  101.  
  102. END
  103.  
  104. SELECT
  105. table_name,
  106. COL_NAME,
  107. value_found
  108. FROM
  109. @resultTable
  110. WHERE
  111. value_found IS not null
  112. ORDER BY
  113. 1, 2, 3
  114.  

Posted in Hacks, Sql | Comments (0)

Drop all foreign keys on a database and optionally drop all tables

January 16th, 2012

If you've ever found the need to drop or remove all foreign keys on a database then here's a little script that runs without cursors.

  1.  
  2. DECLARE @SQL NVARCHAR(MAX)
  3.  
  4. SELECT
  5. @SQL = '-- Start '
  6.  
  7. WHILE @SQL > ''
  8. BEGIN
  9. SELECT
  10. @SQL = ''
  11.  
  12. SELECT
  13. @SQL = @SQL +
  14. CASE
  15. WHEN DATALENGTH(@SQL) < 7500 THEN
  16. N'alter table ' + QUOTENAME(schema_name(schema_id))
  17. + N'.'
  18. + QUOTENAME(OBJECT_NAME(parent_object_id))
  19. + N' drop constraint '
  20. + name
  21. + CHAR(13) + CHAR(10) --+ 'GO' + CHAR(13) + CHAR(10)
  22. ELSE
  23. ''
  24. END
  25. FROM
  26. sys.foreign_keys
  27.  
  28. PRINT @SQL
  29. EXEC SP_EXECUTESQL @SQL
  30. PRINT '---8<------------------------------------------------------------------------------------------'
  31. END
  32.  
  33. GO
  34.  
  35. ---8<-------------------------------------------------------------
  36. -- Uncomment the line below to drop all tables too
  37.  
  38. --exec sp_msforeachtable 'drop table ?'
  39.  
  40. PRINT 'All done'
  41.  

Posted in Hacks, Sql | Comments (1)

T-SQL List all columns in all tables

January 13th, 2012

If you've ever wanted to get a listing of all the columns in all of the tables in a certain database, here's how. I use this often for writing technical documentation on systems.

This little code will list all of your tables, columns, datatypes, nullable option, identity option and will also indicate primary and foreign keys and default constraints.

  1.  
  2. SELECT
  3. -- distinct
  4. -- quotename(schema_name(tbl.schema_id)) + N'.' + quotename(tbl.name) as TableName,
  5. -- col.column_id,
  6. col.name AS ColumnName,
  7. type_name(col.user_type_id)+
  8. CASE
  9. WHEN COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'scale') IS null THEN
  10. ' (' + CONVERT(VARCHAR,COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'precision')) + ')' --is null --convert(varchar, col.max_length)
  11. WHEN col.user_type_id in (60, 106, 108, 122) THEN
  12. ' (' + CONVERT(VARCHAR,col.PRECISION) + ',' + CONVERT(VARCHAR, col.scale) + ')'
  13. ELSE ''
  14. END AS DataType,
  15. CASE col.is_nullable
  16. WHEN 0 THEN 'NOT '
  17. ELSE ''
  18. END
  19. + 'NULL' AS Nullable,
  20. CASE col.is_identity
  21. WHEN 0 THEN ''
  22. ELSE 'IDENTITY (' + CONVERT(VARCHAR, idc.seed_value) + ',' + CONVERT(VARCHAR, idc.increment_value) + ')'
  23. END AS [IDENTITY],
  24. CASE
  25. WHEN ixc.column_id IS not null THEN
  26. 'PK'
  27. WHEN fkc.constraint_object_id IS not null THEN
  28. QUOTENAME(schema_name(tb2.schema_id)) + N'.' + QUOTENAME(tb2.name) + N'.' + fcl.name
  29. ELSE ''
  30. END AS Key_Constraint,
  31. CASE
  32. WHEN dfc.OBJECT_ID IS not null THEN
  33. dfc.definition
  34. ELSE ''
  35. END AS [DEFAULT CONSTRAINT]
  36. FROM
  37. sys.tables tbl
  38. INNER join
  39. (
  40. SELECT
  41. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
  42. col.name,
  43. col.column_id,
  44. col.OBJECT_ID,
  45. col.is_identity,
  46. col.is_nullable,
  47. col.PRECISION,
  48. col.scale,
  49. col.user_type_id,
  50. 1 AS SortKey
  51. FROM
  52. sys.columns col
  53. INNER join
  54. sys.tables tbl
  55. ON tbl.OBJECT_ID = col.OBJECT_ID
  56.  
  57. UNION
  58.  
  59. SELECT
  60. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
  61. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
  62. 0,
  63. tbl.OBJECT_ID,
  64. 0,
  65. 0,
  66. 0,
  67. 0,
  68. 0,
  69. 0
  70. FROM
  71. sys.tables tbl
  72.  
  73.  
  74. ) col
  75. ON tbl.OBJECT_ID = col.OBJECT_ID
  76. LEFT join
  77. sys.key_constraints pkc
  78. ON pkc.parent_object_id = tbl.OBJECT_ID
  79. and pkc.type = 'PK'
  80. LEFT join
  81. sys.index_columns ixc
  82. ON ixc.OBJECT_ID = tbl.OBJECT_ID
  83. and ixc.index_id = pkc.unique_index_id
  84. and ixc.column_id = col.column_id
  85. LEFT join
  86. sys.foreign_key_columns fkc
  87. ON fkc.parent_object_id = tbl.OBJECT_ID
  88. and fkc.parent_column_id = col.column_id
  89. LEFT join
  90. sys.columns fcl
  91. ON fkc.referenced_column_id = fcl.column_id
  92. and fkc.referenced_object_id = fcl.OBJECT_ID
  93. LEFT join
  94. sys.tables tb2
  95. ON fcl.OBJECT_ID = tb2.OBJECT_ID
  96. LEFT join
  97. sys.default_constraints dfc
  98. ON dfc.parent_object_id = tbl.OBJECT_ID
  99. and dfc.parent_column_id = col.column_id
  100. LEFT join
  101. sys.identity_columns idc
  102. ON idc.OBJECT_ID = tbl.OBJECT_ID
  103. and idc.column_id = col.column_id
  104. WHERE
  105. tbl.type = 'U'
  106. ORDER BY
  107. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name),
  108. SortKey,
  109. ColumnName
  110.  

Posted in Hacks, Sql | Comments (0)

SEO Powered by Platinum SEO from Techblissonline