Can I dynamically (from a stored procedure) generate
a create table script of all tables in a given database (with defaults etc)
a create view script of all views
a create function script of all functions
a create index script of all indexes.
(The result will be 4 scripts)
Arno de Jong,
The Netherlands.
Hi,
I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.
Thanks.
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
View 2 Replies View Relatedthe subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies View RelatedIs it possible to create a temp table using exec. e.g
exec('create table #temp1 (xyz int, abc int)')
when I run the above statement, I dont get an error but nothing happens.
The reason I need to create it dynamically is that in my stored proc, I am passed a number. This number determines how many columns my temp table should have.
thanks
Zoey
The following dynamic SQL script works for creating a table on the fly but if I change the select @tmpTblNm = 'tmpABC' to select @tmpTblNm = '#tmpABC'
it will not create the temp table. Can anyone help on creating a temp table dynamiclly?
declare @tmpTblNm varchar(40),
@str varchar(1000)
select @tmpTblNm = 'tmpABC'
select @str = ''
-- Create a temp table to hold the current page of data
-- Add an ID column to track the current row
select @str = 'Create Table '+ @tmpTblNm +
' ( row_id int IDENTITY PRIMARY KEY,
customerID int,
customerName varchar(40),
address1 varchar(40),
city varchar(25) )'
exec (@str)
Hi guru !!
I want to create a table that can created dynamicly based on front end application.table should handle insertion and edit also. please help me to get out of this situation.
thanks
shekhar
Hi,
I'm trying to import a csv file directly to my database every month. The contents of the file stays the same, however the format of the columns may vary. For example, 1 month I can have the following:
Time, Probe1, Probe2, Probe3, Probe4
Whereas the next month I can have something like this
Time, Probe2, Probe3, Probe1, Probe4
The "Time" column will always be on the left side, but the probes may vary in their placement.
I'm importing this csv to a temp table, where I then run a query to select any new data and enter that in my main tables. The problem is that when i import the csv, if the placement of the columns has changed, the data gets entered in the wrong locations.
Is there any way to create this temp table dynamically, based on what the header columns of the csv file are?
Any help appreciated!
Hi,
I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...
Ex: TABLE_A Data
Column Names
Col1
Col2
Col3
Col4...
Some column Names are known already...I need help in writinf the Create table statement..
I need to write something like
Create table Table1
(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +
Address nvarchar(500))....
Now the Table1 should look like
Serial No Name Col1 Col2 Col3 Col4 Address
Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....
Thanks
for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)
CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]
CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]
and that one
CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE
thank you
Hi all
as i remember i had read in Books Online that on each Table in Sql Server we can create only one Clustered index
but today suddenly i create another clustered index on a table without any Error from SQl server !!!
BUT my query Order changed to the order of this newly created index.
could anyone elaborate on this issue?
Thanks in advance.
Regards.
SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)that we need to add some indexes for. In a test, it took over 12 hoursto CREATE a new INDEX against this table. One of us suggested that wecreate a temp table with the new index and copy the data from the oldtable into the new one, then rename it. I understand this took 15minutes. Why the heck would it be faster to move the data and buildmultiple indexes incrementally vs adding an index??
View 11 Replies View RelatedHi all
I am in poduction Support,I have scenario I have to drop the Index .....and ....Create index(cluster and non-cluster) on a table.
will Create index take table off line.
Hi guys,
Can anyone please tell me how to create a table based on a query.
Thanks,
Joe
Hi All,
I'm working on a web application where the user needs to be able to create and name new databases that are identical in structure to other existing databases (that is, all tables, stored procedures, functions, indexes, etc.). This is so that they can create a new database for each client and need to be able to do this through the web application. Having hunted around a fair bit, I've established that SMO is capable of doing pretty much everything that I want. The only problem is that everything I do seems to be based on the actual SQL Server and associated databases rather than the ones I have created in the App_Data folder.
The relevant code (so far) is:Dim sqlServer As New Server()
With sqlServer.ConnectionContext
.ServerInstance = "(local)"
.Connect()
.Disconnect()
End With
For Each db As Database In sqlServer.Databases
ListView1.Items.Add(db.Name)
Next
Dim newDatabase As New Database(sqlServer, DbName.Text.ToString)
newDatabase.Create()
This does actaully create a new database, just not where I want it! Can anyone point me in the right direction as to how I can create a copy of a database in the App_Data folder?
Thanks & regards,
Paul
hi,
in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.
Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3
help is appreciated
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Why should we consider selectivity of a table to create index?
Which is best selectivity value to create an index ?
Hello,
I'm looking for the best solution for creating database tables based on
a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.
What I assume is I would need first to start of by creating two tables.
One table would consist of the fields of the horizontal upper most data e.g.
conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?
Any help would be much appreciated.
Thanks
Karl
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?
Example:
I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.
I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.
BTW - this would be a stored procedure...
Any suggestions would be greatly appreciated.
Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.
my actual table is like
Key
Value
X1
x
X3
x
X5
x
Y1
y
Y2
y
when user select x, the input variable passed to stored proc and the result is shown like
column names
X1 X3 X5 as column headers.
the select query is from temporary table.
these out put is based on the user selection. so the temporary table created with columns dynamically.
please help me out.
please let me know if you didn't understand.
thanks
Praveen.
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.
Here is my script:
CREATE CLUSTERED INDEX CI_IndexName
ON TableName(Column1,Column2)
WITH (MAXDOP= 4, ONLINE=ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION=PAGE)
ON sh_WeekDT(Day_DT)
GO
I am trying to run an SELECT statement with a CONTAINS statement from a aspx.net solution built using Visual Web Developer 2005 express edition. When I try to run the thing it throws an error saying
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'JournalArticle' because it is not full-text indexed.".
I don't have access to the SQL Express server, I interface through Server Management Studio Express. There is an option to create a full-text index from the menu, but it is greyed out, presumably because there is no full-text catalogue. This is my real question, How do I create a full-text catalogue using Server Management Studio Express? The help function only provides examples of sql code, which I assume must be performed using sql server (which I don't have access to). Any help would be greatly appreciated.
cheers,
Bernie
Hi,
Can this be done? Please advise. Thanks.Is there any template available in sql for creating forms?
Hi all,
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...
DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)
I tryed all below senarios...it is giving error...
--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
i have created a fact table which has unique cluster index as below,
CREATE UNIQUE CLUSTERED INDEX [FactSales_SalesID] ON [dbo].[FactSales] (salesid ASC)
WITH (DATA_COMPRESSION = PAGE)
GO
however later when i add CLUSTERED COLUMNSTORE INDEXES :
CREATE CLUSTERED COLUMNSTORE INDEX CSI_FactSales
ON dbo.FactSales WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
it prompts error.
Msg 35372, Level 16, State 3, Line 167 You cannot create more than one clustered index on table 'dbo.FactSales'. Consider creating a new clustered index using 'with (drop_existing = on)' option.
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')
I am using the following script to check existence of table in the Database and create it dynamically...
This is working when table not existed, it error-ed when the table existed...
This script i am using in the Exec Sql Task.....
[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.
declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')
[Code] ...........
What are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.
Any help is much appreciated. Thanks.
I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,
[Code] ....