It seems that I'm repeatedly needing to use some functionality or utility of SQL. So here's a few, for my own reference...
------------------------------------- 2013-06-12
Table Counts - Local and SQL Azure Friendly
---- ROW COUNTS IN LOCAL SQL TABLES ----
SELECT
t.TABLE_SCHEMA + '.' + t.TABLE_NAME as 'TableName'
,[RowCount] = SUM(sp.[rows])
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.partitions sp
ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
WHERE TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
ORDER BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
-----------------------------------
---- ROW COUNTS IN SQL AZURE ----
select
t.schema_id,t.name as 'TableName'
, s.row_count as 'RowCount'
from sys.tables t
join sys.dm_db_partition_stats s ON t.object_id = s.object_id
and t.type_desc = 'USER_TABLE'
and s.index_id = 1
ORDER BY t.schema_id,t.name
---------------------------------
--------------------------------------
For SQL Server installed versions info...------------------------------------- 2013-06-12
Table Counts - Local and SQL Azure Friendly
---- ROW COUNTS IN LOCAL SQL TABLES ----
SELECT
t.TABLE_SCHEMA + '.' + t.TABLE_NAME as 'TableName'
,[RowCount] = SUM(sp.[rows])
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.partitions sp
ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
WHERE TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
ORDER BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
-----------------------------------
---- ROW COUNTS IN SQL AZURE ----
select
t.schema_id,t.name as 'TableName'
, s.row_count as 'RowCount'
from sys.tables t
join sys.dm_db_partition_stats s ON t.object_id = s.object_id
and t.type_desc = 'USER_TABLE'
and s.index_id = 1
ORDER BY t.schema_id,t.name
---------------------------------
--------------------------------------
select serverproperty('Edition') as SQLEdition,
Serverproperty('ProductLevel') as ServicePack,
ServerProperty('ProductVersion')as Version
ServerProperty('ProductVersion')as Version
----------------------------------------------
To save your SELECT query results to a text file, use this query :EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'
--------------------------------------------------------
Over at another blog, there's TONS of good stuff.
http://www.sqlservercurry.com/2008/02/how-to-send-email-using-sql-server-2005.html
-----------------------------------------
Use this query to get all the tables and stored procedures in a database
http://www.sqlservercurry.com/2007/12/display-all-tables-and-stored.html
USE DBNAME
GO
SELECT *
FROM Sys.Objects
WHERE Type IN ('U', 'P')
ORDER BY type_desc
------------------------------------------------
Display the size of all tables in Sql Server 2005http://www.sqlservercurry.com/2008/02/display-size-of-all-tables-in-sql.html
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
---------------------------------------------
Check if a user has access to a database in Sql Server 2005http://www.sqlservercurry.com/2008/03/check-if-user-has-access-to-database.html
SELECT [Name] as DatabaseName from master.dbo.sysdatabases
WHERE ISNULL(HAS_DBACCESS ([Name]),0)=1
ORDER BY [Name]
Another way:
sp_helplogins
sp_helplogins 'LoginName'
----------------------------------------------------
Copy a table from one database to another in SQL Server 2005http://www.sqlservercurry.com/2008/03/copy-table-from-one-database-to-another.html
To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1.
-----------------------------------------------------
Find out the tables having the largest size in your database using SQL Server 2005 http://www.sqlservercurry.com/2008/04/find-out-tables-having-largest-size-in.html
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL,
row varchar(10),
reserved varchar(10),
data varchar(10),
index_size varchar(10),
unused varchar(10)
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name)
SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name
FROM @TblNames
WHERE COUNTER = @I
INSERT INTO @TblNames
EXEC sp_spaceused @str
SET @I = @I +1
END
-- Display results in Sorted order
SELECT tbl_name as TableNm,
CAST(REPLACE(data, ' kb', '') as int) as TblSize,
CAST(REPLACE(index_size, ' kb', '') as int) as IdxSize
FROM @tblNames
ORDER BY TblSize DESC
-------------------------------------------
Populate a table from a .CSV or .TXT file using SQL Server 2005
http://www.sqlservercurry.com/2008/04/populate-table-from-csv-or-txt-file.html
-----------------------------------
DATENAME() FunctionDECLARE @Dt datetime
SET @Dt = '2008-04-15 8:34:54.713'
SELECT @Dt as 'Full Date String',
DATENAME(year, @Dt) as 'Year', -- Returns 2008
DATENAME(quarter, @Dt) as 'Quarter', -- Returns 2
DATENAME(month, @Dt) as 'Month', -- Returns April
DATENAME(dayofyear, @Dt) AS 'Day of Year',-- Returns 106
DATENAME(day, @Dt) AS 'Day',-- Returns 15
DATENAME(week, @Dt) AS 'Week',-- Returns 16
DATENAME(weekday, @Dt) AS 'Weekday',-- Returns Tuesday
DATENAME(hour, @Dt) AS 'Hour',-- Returns 8
DATENAME(minute, @Dt) AS 'Minutes',-- Returns 34
DATENAME(second, @Dt) AS 'Seconds',-- Returns 54
DATENAME(millisecond, @Dt) AS 'Milliseconds'-- Returns 713
DATEPART() Function
SELECT
DATEPART(year, GETDATE()) as 'Year',
DATEPART(month,GETDATE()) as 'Month',
DATEPART(day,GETDATE()) as 'Day',
DATEPART(week,GETDATE()) as 'Week',
DATEPART(hour,GETDATE()) as 'Hour',
DATEPART(minute,GETDATE()) as 'Minute',
DATEPART(second,GETDATE()) as 'Seconds',
DATEPART(millisecond,GETDATE()) as 'MilliSeconds'
----------------------------------------------------
How to Send Email from SQL 2005
http://www.sqlservercurry.com/2008_02_01_archive.html
With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However in Sql Server 2005, we can now use the Database Mail to send emails.[Note: I assume you have set up and configured Database Mail. If not, check this link Database Mail Configuration Stored Procedures to use various stored procedures required to configure Database Mail. As an alternative, you could also use the SQL Server Surface area configuration tool to configure Database Mail]
Use the following script to send a mail from your Sql Server
USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'admin@xyz.com; mailto:pqr@xyz.com,
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;
GO
With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However in Sql Server 2005, we can now use the Database Mail to send emails.[Note: I assume you have set up and configured Database Mail. If not, check this link Database Mail Configuration Stored Procedures to use various stored procedures required to configure Database Mail. As an alternative, you could also use the SQL Server Surface area configuration tool to configure Database Mail]
Use the following script to send a mail from your Sql Server
USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'admin@xyz.com; mailto:pqr@xyz.com,
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;
GO
---------------------------------------------------
Compare Data Between Two Tables
The 'tablediff' utility is a new feature in SQL Server 2005 used to compare differences of schema and data between source and destination table or view. This utility is particularly useful while replicating data.
You can run this utility from the command line or a batch file.
Example:
Let us use the Customers table of the Northwind database as an example. Do the following :
1. Create another table called CustomerTemp.
SELECT * INTO CustomersTemp FROM Customers
2. Now change data in the CustomersTemp table
UPDATE CustomersTemp
SET City = 'Bern'
WHERE CUSTOMERID = 'ALFKI'
3. Run the TableDiff utility
The syntax for running this utility is as follows :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp"
where SUPROTIM is the servername, Northwind is the database, Customers is the source table and CustomerTemp is the destination table
The output is:
Microsoft (R) SQL Server Replication Diff ToolCopyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:-sourceserver SUPROTIM-sourcedatabase Northwind-sourcetable Customers-destinationserver SUPROTIM-destinationdatabase Northwind-destinationtable CustomersTemp
Table [Northwind].[dbo].[Customers] on SUPROTIM and Table [Northwind].[dbo].[CustomersTemp] on SUPROTIM have 1 differences.Err CustomerIDMismatch 'ALFKI'The requested operation took 0.244125 seconds.
If you want to generate the SQL scripts for the differences found, use the -f parameter with the file name:
Example :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp" -f "C:\temp"
You can run this utility from the command line or a batch file.
Example:
Let us use the Customers table of the Northwind database as an example. Do the following :
1. Create another table called CustomerTemp.
SELECT * INTO CustomersTemp FROM Customers
2. Now change data in the CustomersTemp table
UPDATE CustomersTemp
SET City = 'Bern'
WHERE CUSTOMERID = 'ALFKI'
3. Run the TableDiff utility
The syntax for running this utility is as follows :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp"
where SUPROTIM is the servername, Northwind is the database, Customers is the source table and CustomerTemp is the destination table
The output is:
Microsoft (R) SQL Server Replication Diff ToolCopyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:-sourceserver SUPROTIM-sourcedatabase Northwind-sourcetable Customers-destinationserver SUPROTIM-destinationdatabase Northwind-destinationtable CustomersTemp
Table [Northwind].[dbo].[Customers] on SUPROTIM and Table [Northwind].[dbo].[CustomersTemp] on SUPROTIM have 1 differences.Err CustomerIDMismatch 'ALFKI'The requested operation took 0.244125 seconds.
If you want to generate the SQL scripts for the differences found, use the -f parameter with the file name:
Example :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp" -f "C:\temp"
---------------------------------------------------
Save Select Query Output To Text File
Ever needed to save the result of your SELECT query to a text file. Well use xp_cmdshell. xp_cmdshell is an extended stored procedure kept in the master database. It issues OS commands directly to the Windows command shell. You need to be a member of the sys_admin group to use this command or have the xp_sqlagent_proxy_account.
To save your SELECT query results to a text file, use this query :
EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'
One word of caution. xp_cmdshell operates synchronously. So the caller has to wait for the control to be returned to him/her until the command-shell command is completed.
Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
To save your SELECT query results to a text file, use this query :
EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'
One word of caution. xp_cmdshell operates synchronously. So the caller has to wait for the control to be returned to him/her until the command-shell command is completed.
Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
----------------------------------------
