When all is said and done, more will be said than done
Create PROCEDURE [dbo].[ts_Dates] @isTo bit AS /* ---------------------------... --Last Dates Display-Value Pair for DropDown List for date greater than ---------------------------... exec [dbo].[ts_Dates] 0 exec [dbo].[ts_Dates] 1 */ declare @minToDate datetime declare @minFromDate datetime select @minToDate = min(<date>) from <table>; select @minFromDate = DATEADD(MONTH,-1,min(<da... from <table>; --print @minToDate --print ......
Thanks to AndraaxAged Yak Warrior http://www.sqlteam.com/foru... Generate insert SQL for a table - Insert this stored proc: create proc [dbo].[generate_inserts] @table varchar(50) --Generate inserts for table @table AS declare @cols varchar(1000) declare @col varchar(50) /* generate_inserts 'PlanLevel1ControlResponsib... */ set @cols='' declare colcur cursor for select column_name from information_schema.columns where table_name=@table open colcur fetch next from colcur ......
---------------------------... --all info from Tables ---------------------------... SELECT * FROM sys.Tables ---------------------------... --Schema.name from Tables ---------------------------... SELECT '['+SCHEMA_NAME(schema_id)+... AS SchemaTable FROM sys.tables ---------------------------... --Schema, name from Tables ---------------------------... ......
Generate create script for all Foreign Keys Original article: http://connectsql.blogspot.... SELECT 'ALTER TABLE '+OBJECT_NAME(F.PARENT_OBJE... ' ADD CONSTRAINT' + F.NAME + ' FOREIGN KEY'+'('+COL_NAME(FC.PARENT... ')'+'REFRENCES '+OBJECT_NAME (F.REFERENCED_OBJECT_ID)+'(' +COL_NAME(FC.REFERENCED_OBJ... FROM SYS.FOREIGN_KEYS AS F INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_IDGet ......
User-Defined Functions Scalar User-Defined Function A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. Table-Value User-Defined Function An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL ......
Select comma separated result from via comma separated parameter PROCEDURE [dbo].[GetCommaSepStringsBy... (@CommaSepNumericIds varchar(max)) AS BEGIN /* exec GetCommaSepStringsByCommaSe... '1xx1, 1xx2, 1xx3' */ DECLARE @returnCommaSepIds varchar(max); with cte as ( select distinct Left(qc.myString, 1) + '-' + substring(qc.myString, 2, 9) + '-' + substring(qc.myString, 11, 7) as myString from q_CoaRequestCompound qc JOIN dbo.SplitStringToNumberTabl... AS ......
Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc: Derived form "Method 6" from a great article: · How to pass a list of values or array to SQL Server stored procedure · http://vyaskn.tripod.com/pa... Create PROCEDURE [dbo].[GetMyTable_ListByCom... (@CommaSepReqIds varchar(500)) AS BEGIN select * from MyTable q JOIN dbo.SplitStringToNumberTabl... AS s ON q.MyTableId = s.ID End ALTER FUNCTION [dbo].[SplitStringToNumberT... ......
List All Primary Keys and Foreign Keys Original article: http://www.sqlteam.com/foru... set nocount on create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name)) create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key ......
find columns and tables in a view SELECT vObj.name AS vName, vObj.id AS vID, vObj.xtype AS vType, dep.depid, dep.depnumber, tObj.name AS tName, col.colid, col.name AS cName FROM sysobjects vObj LEFT OUTER JOIN sysdepends dep ON vObj.id = dep.id LEFT OUTER JOIN sysobjects tObj ON dep.depid = tObj.id LEFT OUTER JOIN syscolumns col ON dep.depnumber = col.colid AND tObj.id = col.id WHERE vObj.xtype = 'V' And vObj.category = 0 and vObj.name = 'vw1_inv_item_det' ORDER BY vObj.name, tObj.name, col.name ......
original article: http://sqlserver2000.databa... Many people have asked if there is a way to make SQL Server behave the way FORMAT works in VB (and FormatDateTime in VBScript). What they'd like to see is the ability to tell SQL Server to format a date with long date and time, or in MM/DD/YYYY format, instead of having to memorize existing format conversion numbers and/or manipulate the strings themselves. For example, to get today's ......
create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID Steps: 1) Paste script below into Query Analyzer 2) set Query Analyzer results to "text" (not "grid" 3) set @v_Tablename = 'myTableName' below 4) set @v_postfix = '_ID' below (not 'myTableName_ID' ) 5) run script with no errors (script produces new script) 6) paste text results (new script) into query Query Analyzer 7) run script with no errors new table and triggers created Set NoCount On Go declare @v_Tablename ......
how to copy text data from one column to another EXEC sp_dboption 'database name', 'select into/bulkcopy', 'true' GO DECLARE @textData varchar(8000) select @textData = textData from table where id = 5 print '@textData' print @textData DECLARE @ptrvalDest binary(16) SELECT @ptrvalDest = TEXTPTR(xsl) FROM table WHERE id = 6 WRITETEXT table.textData @ptrvalDest @textData GO EXEC sp_dboption 'database name', 'select into/bulkcopy', 'false' GO ......
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [Rodney Vinyard].[function_TodayIsR... -- ===========================... -- Author: <Rodney Vinyard> -- Create date: <1/31/07> -- Description: -- Does @userDate match up with @FrequencyCode & @FrequencyValue? -- -- <for input (1) @userDate (cannot use getDate() here in Function -- (2) @FrequencyCode -- (3) @FrequencyValue, returns 1 - true or 0 - false> -- ===========================... ......
SQL Server 2000 Performance Tuning Tools original article: http://www.sqlteam.com/item... This article comes to us from Brad McGehee at www.sql-server-performance.... SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. Query Analyzer Profiler Index Wizard Performance Monitor SQL Server 2000 Query Analyzer for developing and debugging Transact-SQL code for performance tuning Transact-SQL code. Show Execution Plan Whenever you ......
SQL Server: SELECT sysobjects.name as "Table", syscolumns.name as "Column" from sysobjects , syscolumns where sysobjects.id = syscolumns.id and sysobjects.xtype = 'u' and syscolumns.name like '%YourFieldNameGoesHere%' order by sysobjects.name, syscolumns.name ---------------------------... --Get Table names and Row Counts ---------------------------... SELECT [TableAndCount] = so.name +' - '+CAST(MAX(si.rows)asvarcha... Rows' , [Name] = so.name FROM sysobjects so, sysindexes ......