In SQL 2000/2005 there is no direct feature for this. Below is the workaround.
INSERT INTO tblSuppServers (ServerName,Type)
(SELECT ‘LAB1′,’Dev’)
UNION
(SELECT ‘LAB2′,’Dev’)
UNION
(SELECT ‘LAB3′,’Dev’)
GO
For SQL 2008:
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (’First’,1),
(’Second’,2),
(’Third’,3),
(’Fourth’,4),
(’Fifth’,5)
Ref:
http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/
http://www.daniweb.com/forums/thread34491.html
Have you used xp_getfiledetails extend stored procedure in SQL Server 2000? This undocumented sp used to get details of a given external files.
For example,
EXEC master.dbo.xp_getfiledetails 'c:\pagefile.sys'
will give you following details
1. Size of the file in Bytes
2.Creation Date
3.Creation Time
4. Last Written Date
5. Last Written Time
6.Last Accessed Date
7.Last Accessed Time
8.Attributes
Problem is this NOT AVAILABLE in SQL SERVER 2005. This is the problem with using undocumented sps. This is why many gurus are advise not to use undocumented sps.
Steve Jones is Maintaining the build list of SQL 2005. You can verify and validate your version by running @@Version Global variable and compare it with sqlservercentral.com’s build list @ http://www.sqlservercentral.com/articles/Administration/2960/
SQL Server 2005 offers an undocumented system stored procedure sp_readerrorlog which calls the extended stored procedure xp_readerrorlog.
This procedure takes four parameters:
1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
If you do not pass any parameters this will return the contents of the current error log.
Read more @ http://www.mssqltips.com/tip.asp?tip=1476
You can execute the SP by executing its name like
EXEC DBO.SPGetForeignKeyInfo
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N’[dbo].[SPGetForeignKeyInfo]‘)
AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
DROP PROCEDURE dbo.SPGetForeignKeyInfo
GO
CREATE PROCEDURE DBO.SPGetForeignKeyInfo
AS
/*
Author : Seenivasan
This procedure is used for Generating Foreign Key script.
*/
SET NOCOUNT ON
DECLARE @FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @FieldNames NVARCHAR(500)
CREATE TABLE #Temp(
PKTABLE_QUALIFIER NVARCHAR(128),
PKTABLE_OWNER NVARCHAR(128),
PKTABLE_NAME NVARCHAR(128),
PKCOLUMN_NAME NVARCHAR(128),
FKTABLE_QUALIFIER NVARCHAR(128),
FKTABLE_OWNER NVARCHAR(128),
FKTABLE_NAME NVARCHAR(128),
FKCOLUMN_NAME NVARCHAR(128),
KEY_SEQ INT,
UPDATE_RULE INT,
DELETE_RULE INT,
FK_NAME NVARCHAR(128),
PK_NAME NVARCHAR(128),
DEFERRABILITY INT)
DECLARE TTableNames CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype = ‘U’
OPEN TTableNames
FETCH NEXT
FROM TTableNames
INTO @fTableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Temp
EXEC dbo.sp_fkeys @fTableName
FETCH NEXT
FROM TTableNames
INTO @fTableName
END
CLOSE TTableNames
DEALLOCATE TTableNames
SET @FieldNames = ”
SET @fTableName = ”
SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName,
@FieldNames AS FTFields,PKTABLE_NAME AS STName,
@FieldNames AS STFields,@FieldNames AS FKType
INTO #Temp1
FROM #Temp
ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME
DECLARE FK_CUSROR CURSOR FOR
SELECT FKName
FROM #Temp1
OPEN FK_CUSROR
FETCH
FROM FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE FK_FIELDS_CUSROR CURSOR FOR
SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
FROM #TEMP
WHERE FK_NAME = @FKName
ORDER BY KEY_SEQ
OPEN FK_FIELDS_CUSROR
FETCH
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields)
= 0 THEN ‘['+@FKColumnName+']‘
ELSE FTFields
+‘,['+@FKColumnName+']‘ END
WHERE FKName = @FKName
UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields)
= 0 THEN ‘['+@PKColumnName+']‘
ELSE STFields
+‘,['+@PKColumnName+']‘ END
WHERE FKName = @FKName
FETCH NEXT
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
END
UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0
THEN FKType + ‘ ON UPDATE CASCADE’
ELSE FKType END
WHERE FKName = @FKName
UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0
THEN FKType + ‘ ON DELETE CASCADE’
ELSE FKType END
WHERE FKName = @FKName
CLOSE FK_FIELDS_CUSROR
DEALLOCATE FK_FIELDS_CUSROR
FETCH next
FROM FK_CUSROR INTO @FKName
END
CLOSE FK_CUSROR
DEALLOCATE FK_CUSROR
SELECT ‘ALTER TABLE [dbo].['+FTName+'] ADD
CONSTRAINT ['+FKName+'] FOREIGN KEY (’+FTFields+‘)
REFERENCES ['+STName+'] (’+STFields+‘) ’+FKType
FROM #Temp1
SET NOCOUNT OFF
RETURN
GO
Read more @ http://blog.sqlauthority.com/2008/04/18/sql-server-generate-foreign-key-scripts-for-database/
While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time. WAITFOR can be used with query but not with UDF or cursors. WAITFOR wait till TIMEOUT is reached.
--Delay for 20 seconds
WAITFOR DELAY ‘000:00:20′
SELECT ‘20 Second Delay’
GO
—Delay till 7 AM
WAITFOR TIME ‘7:00:00′
SELECT ‘Good Morning’
GO
To Read more Click the below link
http://blog.sqlauthority.com/2007/06/18/sql-server-delay-function-waitfor-clause-delay-execution-of-commands/
T-SQL by Gibson to get the complete list of Tables and its columns and associated index…
Click here to download the Script
Sample Output:
Microsoft included several hundred stored procedures in the various versions of Microsoft SQL Server and it has documented a good percentage of them. But many stored procedures remain undocumented. Some are used within the Enterprise Manager GUI in SQL 2000 and were not intended to be used by other processes. Microsoft has slated some of these stored procedures to be removed (or they have been removed) from future versions of SQL Server. While these stored procedures can be very useful and save you lots of time, they can be changed at any time in their function or they can simply be removed.
List of Stored Procedures:
Complete Article:
http://searchsqlserver.techtarget.com
This is a 2005 sort of sp_who3, which’ll return connection info as well as the running command. Also included is the SQL Server 2000 equivalent and also a means of seeing zero cost plans.
/********* live requests (running ones) *************/
SELECT st.text as [Command text],
login_time,
[host_name],
program_name,
sys.dm_exec_requests.session_id,
client_net_address,
sys.dm_exec_requests.status,
command,
db_name(database_id) as DatabaseName
FROM sys.dm_exec_requests
inner join sys.dm_exec_connections on sys.dm_exec_requests.session_id = sys.dm_exec_connections.session_id
inner join sys.dm_exec_sessions on sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
cross apply sys.dm_exec_sql_text(sql_handle) AS st
WHERE sys.dm_exec_requests.session_id >= 51
GO
Sample Output:
Click on the above image to get enlarge view of the output….