SELECT t.NAME AS TableName, p.[Rows], count(c.name) as Columns FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.columns c on t.OBJECT_ID=c.OBJECT_ID WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 AND i.index_id <= 1 GROUP BY t.NAME, i.object_id, i.index_id, i.name, p.[Rows] ORDER BY object_name(i.object_id)
When you have many databases and want to iterate a command on them quickly, use the sp_MSforeachdb command. For example:
DECLARE @command varchar(1000) SELECT @command = 'SELECT * FROM information_schema.tables' EXEC sp_MSforeachdb @command
I inherited a project where I needed to create a physical file of data, encrypt it, and FTP it daily. First, I created my script, and when it was completed, added it to the SQL Agent jobs. Code snippets follow: Continue reading →
UPDATE T SET IS_ACTIVE= ( select IsActive FROM Staff S WHERE S.EmployeeNum=T.EmployeeNum ) FROM MyTable T WHERE T.EmployeeNum IS NOT NULL
The Merge feature is new as of SQL 2008, and it’s great for doing exactly that. However, you can only have one when matched clause, and only one when not matched .
MERGE Mammals AS TARGET USING( SELECT C.LatinName, C.CommonName, C.Lifespan FROM Camels C ) AS SOURCE (LatinName, CommonName, Lifespan) ON TARGET.LatinName=SOURCE.LatinName WHEN MATCHED THEN -- update conditionally. Cases are : normal update, update when newly inactive UPDATE SET CommonName= CASE WHEN SOURCE.LatinName='ABC' THEN 'Brown Camel' WHEN SOURCE.IsActive='DEF' THEN 'Red Camel' END , Lifespan=SOURCE.Lifespan WHEN NOT MATCHED THEN -- does not exist, new record, insert it INSERT (LatinName, CommonName, Lifespan) VALUES (SOURCE.LatinName, SOURCE.CommonName, SOURCE.Lifespan );
There’s been a small change to the way MS Access works from the ’97 to the 2010 version that can cause some serious issues.
Consider this query “Select * From Table1″ . What happens? In both versions, it will run just fine. However, when importing this command through any database connection, say OLEDB, it won’t work. Instead, what’s fed into 2010 is “able1″.
What’s happening? For database connections, table names are required to be square-bracketed. A simple fix.
Just use “Select * From [Table1]” instead.
I recently encountered this. No calls would go through, even though it was working yesterday.
– remove the battery
– remove the sim card
– reinsert both
– (re)start your phone.