SQL Server 2000 Tricks

Last n Months Display-Value Pair for DropDown List greater than a minimumm date

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Tuesday, January 31, 2012 6:43 AM | Feedback (0)

Generate insert SQL for a table

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Thursday, January 12, 2012 9:17 AM | Feedback (0)

list schema, table, row count in a database

---------------------------... --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 ---------------------------...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Sunday, January 08, 2012 7:40 PM | Feedback (0)

Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

Display Foreign Key Relationships and Name of the Constraint for Each Table in Database Original Article: http://blog.sqlauthority.co... I changed to version below to 1) show PK to the left 2) trim the result columns SELECT PK_Table =Left(PK.TABLE_NAME, 35), PK_Column =Left(PT.COLUMN_NAME, 12), FK_Table =Left(FK.TABLE_NAME, 25), FK_Column =Left(CU.COLUMN_NAME, 20), Constraint_Name...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Thursday, December 15, 2011 7:04 AM | Feedback (1)

SQL Server Interview Questions

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Saturday, February 26, 2011 2:43 PM | Feedback (1)

Select comma separated result from via comma separated parameter

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Monday, February 14, 2011 11:34 AM | Feedback (13)

Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Friday, May 28, 2010 1:56 PM | Feedback (2)

List All Primary Keys and Foreign Keys

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Thursday, October 23, 2008 3:49 AM | Feedback (3)

find columns and tables in a view

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Tuesday, April 29, 2008 4:54 AM | Feedback (2)

SQL Server format dates and times

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Saturday, April 26, 2008 4:59 AM | Feedback (2)

create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Thursday, March 15, 2007 10:47 AM | Feedback (3)

how to copy text data from one column to another

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Wednesday, February 21, 2007 1:51 PM | Feedback (2)

@userDate match up with @FrequencyCode & @FrequencyValue

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> -- ===========================...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Wednesday, January 31, 2007 10:27 AM | Feedback (2)

SQL Server 2000 Performance Tuning Tools

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Monday, August 07, 2006 5:34 PM | Feedback (2)

Find any datasbase Column by partial Name in any User Table

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...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Saturday, July 15, 2006 2:47 PM | Feedback (2)

 
Powered by:

Copyright © Rodney Vinyard