↓ Skip to main content

SQL

SQL Record Count

SQL Record Count

·1 min
The fastest way to perform a SQL record count is: DECLARE @TableName sysname SET @TableName = 'Log' SELECT TBL.object_id, TBL.name, SUM(PART.rows) AS rows FROM sys.tables TBL INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id AND PART.index_id = IDX.index_id WHERE TBL.name = @TableName AND IDX.index_id < 2 GROUP BY TBL.object_id, TBL.name;
How to export all tables to csv by one export job

How to export all tables to csv by one export job

·1 min
If you just want to export all the tables in a SQL database in to separate CSV files, then this is a quick and easy way of doing it. Execute below query which generates BCP commands SELECT ‘bcp ‘ + st.NAME + ‘ out c:\Target\’ + st.NAME + ‘.csv -c -r -d ‘ + DB_NAME() + ‘ -U user@??????.database.windows.net -S tcp:?????.database.windows.net -P ?????? FROM sys.tables st Paste the result set into text file. Make batch file and schedule it. (It can also be run in CMD manually)
Break apart the connection string

Break apart the connection string

·1 min
It is sometimes useful to know which database you are connected to, here is a simple method that pulls apart the connection string and provides you with the section you request. private static string GetSection(string section) { var connectionString = string.Empty; Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MyWebSiteRoot"); ConnectionStringSettings connString; if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0) { connString = rootWebConfig.ConnectionStrings.ConnectionStrings[“Entities”]; if (connString != null) connectionString = connString.ConnectionString; } string[] parts = connectionString.Split(’;’); for (int i = 0; i < parts.Length; i++) { string part = parts[i].Trim().ToLowerInvariant(); if (part.StartsWith($"{section}=")) { return part.Replace($"{section}=", “”); } } return “Unknown”; }
Search every column in a SQL Server Database

Search every column in a SQL Server Database

·1 min
Following on from the Search every table and field in a SQL Server Database article I posted last week, I thought it would be useful also to be able to search every column for some text and return where it was found.
Search every column in a SQL Server Database

Search every column in a SQL Server Database

·1 min
Following on from the Search every table and field in a SQL Server Database article I posted last week, I thought it would be useful also to be able to search every column for some text and return where it was found.
Search every table and field in a SQL Server Database

Search every table and field in a SQL Server Database

·2 mins
One thing I do like about MySQL is when using phpMyAdmin is the ability to search the whole database for any string, this should only be available for developers who are searching for data in an extensive database. It does take some time to run, but remember it has a lot to do.
Database Layer

Database Layer

·1 min
The DatabaseLayer is an abstraction for database access which means the calling application or library does not need to be tight-coupled to the database itself. A set of factory methods is available to call in order to create commands and parameters, all based on the .NET abstract base classes.

ttt

·2 mins
I can’t believe I couldn’t find a working solution to this after an hour of searching. I’m following this articleon Entity Framework 6.0 which gives a simple walk-through on Code First. I created the project and installed the latest EF Nuget package for the project to compile. I also verified that I have Microsoft SQL Server 2012 Express LocalDB installed which came with Visual Studio 2013. I don’t have any other instances of SQL installed on my local computer. The program runs and entries are added to the database and outputted in the console. But when the article says “check your localdb” it doesn’t say how! I don’t see any ‘.mdf’ or ‘.ldf’ files created under the project folder. I tried every way to connect Visual Studio’s Server Explorer to LocalDB. The wizard cannot locate (localdb) or cannot find any provider in Server Explorer to accept connection string like (localdb)\v11.0;Integrated Security=true;" I’ve seen this asked several places in StackOverflow but no answer works or marked as answer. Please help, this doesn’t have to be this frustrating!

How to Rank your search results with multiple search terms using LINQ and EntityFramework

·2 mins
I have always used a ranked search criteria, it’s the only true way to get good results back from a data set. But I’ve been doing my ranking within C# code. But this got all the data from the data source and then ranked the results. This is very poor on performance, as we should only be returning back the results for the page size we require.

Backup SQL Azure

·7 mins
There are a number of options for backing up SQL Azure, which can be found here: Different ways to Backup your Windows Azure SQL Database I like the Azure way, which is just exporting, importing and setting a scheduled

Migration from SQL to SQL Azure

·1 min
Tasks in Migration Migrate users and login ID’s Map data types Map database objects: tables, triggers, views, stored procedures Update incompatible objects to work with SQL Azure Update application to work with SQL Azure Migrate data to SQL Azure

SQL Performance Profiler for SQL Express

·1 min
I came across this smart little Profiler for SQL Express, or any version of SQL as it happens. Microsoft SQL Server family includes free Express edition, that is fully functional, however has some disappointing limitations which prevent from using it in development process. One of them is absense of profiling tools, standard SQL profiler is not included. However, I came across this SQL Performance Profiler to use with the express edition for tuning your system.

Search every SQL table for a string

·1 min
Here is some neat SQL to allow you to search every table and every field in the database for a string CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int) GO SET NOCOUNT ON DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int SELECT @Sargable = ‘There are too many subscription products resulting from the supplied request data’ DECLARE insaneCursor CURSOR FOR SELECT c.TABLE_NAME, c.COLUMN_NAME FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME WHERE c.DATA_TYPE IN (‘char’,’nchar’,‘varchar’,’nvarchar’,’text’,’ntext’) AND t.TABLE_TYPE = ‘BASE TABLE’ OPEN insaneCursor FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = ‘INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT ' + ’’’’ + @TABLE_NAME + ’’’’ + ‘,’ + ’’’’ + @COLUMN_NAME + ’’’’ + ‘,’ + ‘COUNT(*) FROM [’ + @TABLE_NAME + ‘] WHERE [’ + @COLUMN_NAME + ‘] Like ' + ‘’’’+ ‘%’ + @Sargable + ‘%’ + ’’’’ –SELECT @SQL EXEC(@SQL) IF @@ERROR <> 0 BEGIN SELECT @SQL SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME GOTO Error END FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME END SELECT * FROM myTable99 WHERE Occurs <> 0 Error: CLOSE insaneCursor DEALLOCATE insaneCursor GO DROP TABLE myTable99 GO SET NOCOUNT OFF

Deploy a database project with TFS Build

·2 mins
When you develop web application you often have this scenario. Some people are testers, they are testing the application in dev all day, and they want always to test latest version. Moreover testers usually fills data into web application, so they want their data to be preserved between various deploy. If you simply clear all data in test database you will end with a lot of furious tester so do not even think to do this. If a developer needs to change database schema, we need to automate not only the deploy of the web application, but we need also to sync test database with the latest changes. This is necessary so the test site can work with the new version, but old data is preserved for the happiness of the testers. This is quite simple using TFS and Database project. The situation is this one

Who is locking my SQL database?

·2 mins
Need to know who is logged on to your SQL database ,here is a nice script that I got from Gary Dunne DECLARE @spid1 int = NULL /* server process id to check for locks */ DECLARE @spid2 int = NULL /* other process id to check for locks */ set nocount on /* ** Show the locks for both parameters. */ declare @objid int, @dbid int, @string Nvarchar(255) CREATE TABLE #locktable ( spid smallint ,loginname nvarchar(20) ,hostname nvarchar(30) ,dbid int ,dbname nvarchar(20) ,objId int ,ObjName nvarchar(128) ,IndId int ,Type nvarchar(4) ,Resource nvarchar(16) ,Mode nvarchar(8) ,Status nvarchar(5) ) if @spid1 is not NULL begin INSERT #locktable ( spid ,loginname ,hostname ,dbid ,dbname ,objId ,ObjName ,IndId ,Type ,Resource ,Mode ,Status ) select convert (smallint, l.req_spid) –,coalesce(substring (user_name(req_spid), 1, 20),’’) ,coalesce(substring (s.loginame, 1, 20),’’) ,coalesce(substring (s.hostname, 1, 30),’’) ,l.rsc_dbid ,substring (db_name(l.rsc_dbid), 1, 20) ,l.rsc_objid ,’' ,l.rsc_indid ,substring (v.name, 1, 4) ,substring (l.rsc_text, 1, 16) ,substring (u.name, 1, 8) ,substring (x.name, 1, 5) from master.dbo.syslockinfo l, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u, master.dbo.sysprocesses s where l.rsc_type = v.number and v.type = ‘LR’ and l.req_status = x.number and x.type = ‘LS’ and l.req_mode + 1 = u.number and u.type = ‘L’ and req_spid in (@spid1, @spid2) and req_spid = s.spid end /* ** No parameters, so show all the locks. */ else begin INSERT #locktable ( spid ,loginname ,hostname ,dbid ,dbname ,objId ,ObjName ,IndId ,Type ,Resource ,Mode ,Status ) select convert (smallint, l.req_spid) –,coalesce(substring (user_name(req_spid), 1, 20),’’) ,coalesce(substring (s.loginame, 1, 20),’’) ,coalesce(substring (s.hostname, 1, 30),’’) ,l.rsc_dbid ,substring (db_name(l.rsc_dbid), 1, 20) ,l.rsc_objid ,’' ,l.rsc_indid ,substring (v.name, 1, 4) ,substring (l.rsc_text, 1, 16) ,substring (u.name, 1, 8) ,substring (x.name, 1, 5) from master.dbo.syslockinfo l, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u, master.dbo.sysprocesses s where l.rsc_type = v.number and v.type = ‘LR’ and l.req_status = x.number and x.type = ‘LS’ and l.req_mode + 1 = u.number and u.type = ‘L’ and req_spid = s.spid order by spID END DECLARE lock_cursor CURSOR FOR SELECT dbid, ObjId FROM #locktable WHERE Type =‘TAB’ OPEN lock_cursor FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId WHILE @@FETCH_STATUS = 0 BEGIN SELECT @string = ‘USE ’ + db_name(@dbid) + char(13) + ‘UPDATE #locktable SET ObjName = object_name(’ + convert(varchar(32),@objId) + ‘) WHERE dbid = ’ + convert(varchar(32),@dbId) + ’ AND objid = ’ + convert(varchar(32),@objId) EXECUTE (@string) FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId END CLOSE lock_cursor DEALLOCATE lock_cursor SELECT * FROM #locktable DROP TABLE #locktable

SQL71566 when upgrading to VS12

·1 min
In VS 2012 I receive the following error message (one for each table) when trying to build a database project. SQL71566: Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlFilegroup cannot not be set on both the Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable and the clustered Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPrimaryKeyConstraint. For reference here is script associated with this error.