Search Tables: SELECT c.name AS ‘ColumnName’ ,t.name AS ‘TableName’ FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName; Search Tables and Views: SELECT COLUMN_NAME AS ‘ColumnName’ ,TABLE_NAME AS ‘TableName’ FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName;
SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) … Read more
The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server. SELECT CAST( CASE WHEN Obsolete=”N” or InStock = ‘Y’ THEN 1 ELSE 0 END AS bit) as Saleable, * FROM Product You only need to use the CAST operator if you want the result as a … Read more
You can use OPENROWSET for this. Have a look. I’ve also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn’t already enabled. CREATE PROC getBusinessLineHistory AS BEGIN SELECT * FROM sys.databases END GO sp_configure ‘Show Advanced Options’, 1 GO RECONFIGURE GO sp_configure ‘Ad Hoc Distributed Queries’, 1 GO RECONFIGURE GO … Read more
In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement. INSERT INTO MyTable ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 ) For reference to this have a look at MOC Course 2778A – Writing SQL Queries in SQL Server 2008. For example: INSERT INTO MyTable … Read more
As per the documentation: FROM (Transact-SQL): <join_type> ::= [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ] JOIN The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note … Read more
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date)) for example SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) gives me 2008-09-22 00:00:00.000 Pros: No varchar<->datetime conversions required No need to think about locale
SQL Server 2005 onwards: IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N’columnName’ AND Object_ID = Object_ID(N’schemaName.tableName’)) BEGIN — Column Exists END Martin Smith’s version is shorter: IF COL_LENGTH(‘schemaName.tableName’, ‘columnName’) IS NOT NULL BEGIN — Column Exists END